does foreign key always reference to a unique key

问题:

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

回答1:

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.

回答2:

Yes, it is possible for a foreign key to reference a column with duplicate values.

This can happen if the primary key uses a non-unique index and is not validated when it is created. (But I have never seen a situation like this in real life. As @Bill Karwin pointed out, it would be very confusing. So this may not be a situation you really need to worry about.)

--Create a table with two duplicate rows
create table test1(a number);
insert into test1 values(1);
insert into test1 values(1);
commit;

--Create a non-unique index
create index test1_index on test1(a);

--Use the non-unique index for the primary key, do not validate
alter table test1 add constraint test1_pk primary key (a)
    using index test1_index novalidate;

--Build another table with a foreign key to TABLE1
create table test2(a number,
    constraint test2_fk foreign key (a) references test1(a));

--Inserting a value that refers to the duplicate value still works.
insert into test2 values(1);
commit;

--The foreign key still works:
--ORA-02291: integrity constraint (TEST2_FK) violated - parent key not found
insert into test2 values(2);

--The primary key works as expected, but only for new values:
--ORA-00001: unique constraint (TEST1_PK) violated
insert into test1 values(1);

回答3:

No, it is not possible.

When you define a foreign key constraint on a table, it means there is only one corresponding key on the foreign table. If multiples existed on the foreign table which one would be meant?

Wikipedia has this definition on the Foreign key entry:

A foreign key is a field in a relational table that matches a candidate key of another table

Candidate keys are unique within a table.