does foreign key always reference to a unique key in another table?

ratsy picture ratsy · Jan 2, 2012 · Viewed 38.2k times · Source

Is it not possible that foreign key(single column) in a child table references to a parent key which has some duplicate values?

Answer

Bill Karwin picture Bill Karwin · Jan 2, 2012

By the SQL standard, a foreign key must reference either the primary key or a unique key of the parent table. If the primary key has multiple columns, the foreign key must have the same number and order of columns. Therefore the foreign key references a unique row in the parent table; there can be no duplicates.


Re your comment:

If T.A is a primary key, then no you can't have any duplicates. Any primary key must be unique and non-null. Therefore if the child table has a foreign key referencing the parent's primary key, it must match a non-null, unique value, and therefore references exactly one row in the parent table. In this case you can't make a child row that references multiple parent rows.

You can create a child row whose foreign key column is NULL, in which case it references no row in the parent table.