Foreign key reference to a two-column primary key

Posted by Adam Ernst on Stack Overflow See other posts from Stack Overflow or by Adam Ernst
Published on 2010-03-19T16:10:32Z Indexed on 2010/03/19 16:41 UTC
Read the original article Hit count: 573

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about schema