Menu
Why is it necessary to have a primary key on a column of one table to which a column of the other table having foreign key references.
create table D(Did int)
create table E(Eid int foreign key references D(Did))
The above query gives error:
There are no primary or candidate keys in the referenced table 'D' that match
the referencing column list in the foreign key 'FK__E__Eid__79C80F94'.
394
From Books Online: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.
You need to have either a primary key or unique key in the column which will be referred by the foreign key. Without it you can't create the foreign key.
Yes, you can make one without a Primary Key (or, another option is a Compound Primary Key - making the two references a unique pair, and using that as the unique identifying key - but even this isn't necessary (note: just because it "isn't necessary" doesn't mean it isn't "good practice"; it wouldn't generally be a ...
Creating a foreign key is almost as easy as creating a primary key, except that SQL Server imposes several more rules on foreign keys. For example, the foreign key must reference a primary key or unique constraint, although that reference can be on the same table or on a different table.
Very good question. There is no fundamental reason why a referential constraint shouldn't reference something other than a candidate key. There is even a name for such constraints: Inclusion Dependencies. A foreign key is just a type of inclusion dependency where the target of the constraint happens to be a candidate key.
Unfortunately SQL doesn't provide good support for inclusion dependencies or even for referential constraints generally. SQL limits its so-called FOREIGN KEY constraints to referencing the columns of a UNIQUE or PRIMARY KEY constraint (not necessarily a candidate key though).
So what you have come up against is really a dubious limitation of SQL. It doesn't mean you are doing anything very wrong.
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
