Friday, February 24, 2012

Could not create a Foreign key on 2 unique columns

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.in order for a compound FK to be declared, it must reference a compound unique key

in other words, it's not enough that ID1 and ID2 are each unique, the pair of them must be unique too|||The bigger questions are why do you have TWO unique columns in a single table, and why would you feel the need to have TWO foreign keys to the same unique record?|||Thank you for you reply.
Yes, I know that in MS Server documentation said those columns in FK must refer to A unique constraint.

My question was “why is not possible..” because mathematical it is possible to unique identify a row in table T_FK with a row in table T_PK.
I put such a question only from this logical point of view not for a need of use..
and maybe an answer could be you first question… Practically it’s no need to have 2 unique columns in the same table.|||why can a compound FK not reference two separately-unique-but-perhaps-invalid-when-combined keys?

becuase of the possibility that together, they might be invalid, i.e. not exist

a FK must refer to a unique row (this is relational integrity), and a unique row must be identifiable by a unique key (this is entity integrity)

if ID1 and ID2 are separately unique, then table1 could have rows like this --

ID1 ID2
101 205
102 206
103 207
104 208

your FK definition is not valid because it would allow {101,208} as a FK which doesn't exist as a PK, even though 101 and 208 do exist and are separately unique

No comments:

Post a Comment