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

oracle key parent
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.

Related questions

Oracle SQL - Add Primary Key to table

I have some columns with no primary key and want to add a primary key column. NAME Age ------------- Peter 45 Bob 25 John 56 Peter 45 Some collegues suggest to add a PK with a sequences and triggers: Add a auto increment primary …

Read More
"SET FOREIGN_KEY_CHECKS = 0;" Oracle Equivalent

Is there some equivalent to the Mysql specific instruction that disable the check of the foreign keys constraints ? SET FOREIGN_KEY_CHECKS = 0;

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More