Foreign key reference to a two-column primary key

Question

I'm building a database that must work on MySQL, PostgreSQL, and SQLite. One of my tables has a two-column primary key:

CREATE TABLE tournament (
    state CHAR(2) NOT NULL, 
    year INT NOT NULL,
    etc..., 
    PRIMARY KEY(state, year)
);

I want a reference to the tournament table from another table, but I want this reference to be nullable. Here's how I might do it, imagining that a winner doesn't necessarily have a tournament:

CREATE TABLE winner (
    name VARCHAR NOT NULL,
    state CHAR(2) NULL,
    year INT NULL
);

If state is null but year is not, or vice-versa, the table would be inconsistent. I believe the following FOREIGN KEY constraint fixes it:

ALTER TABLE winner ADD CONSTRAINT FOREIGN KEY fk (name, state) REFERENCES tournament (name, state);

Is this the proper way of enforcing consistency? Is this schema properly normalized?

Answer 1

Rule #1: ALWAYS SAY THE DATABASE YOU'RE USING

Ok, I'm going to suggest you look at the ON DELETE clause, and the MATCH clause. Because, Pg is fairly SQL compliant I'll point you to the current docs on CREATE TABLE.

Excerpt:

These clauses specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table. If refcolumn is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table. Note that foreign key constraints cannot be defined between temporary tables and permanent tables.

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented.

In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually changed, no action is done. Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable. There are the following possible actions for each clause:

Also, there is a major exception here with MS SQL -- which doesn't permit partial matches (MATCH SIMPLE and MATCH PARTIAL) behaviors in foreign keys (defaults and enforces MATCH FULL). There are workarounds where you create a MATCH FULL index on the part of the table that IS NOT NULL for any of the composite key's constituents.