create foreign key without a primary key

Question

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'.

Answer 1

Without a foreign key on D, records in E have no way of knowing, which record is referenced.

Answer 2

I thinks the PK it's used internally so the sql server knows on what line to operate on. If you don't have a PK and put the same values on two different rows then the sql server will have trouble processing the commands.

Answer 3

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.

Answer 4

Easy. If you have 2 values the same in the parent table, how do you know which one to associate child rows to? One side of foreign key must be unambiguous

The requirement is also "unique key", not just a primary key which of course unique