Is it not possible that foreign key(single column) in a child table references to a parent key which has some duplicate values?
A foreign key can refer to either a unique or a primary key of the parent table. If the foreign key refers to a non-primary unique key, you must specify the column names of the key explicitly.
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.
A table can have multiple foreign keys based on the requirement.
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.
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