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: 569
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