oracle 外键是否总是引用另一个表中的唯一键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8706073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
does foreign key always reference to a unique key in another table?
提问by ratsy
Is it not possible that foreign key(single column) in a child table references to a parent key which has some duplicate values?
子表中的外键（单列）是否可能引用具有某些重复值的父键？
回答by Bill Karwin
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.
根据 SQL 标准，外键必须引用父表的主键或唯一键。如果主键有多个列，外键的列数和顺序必须相同。因此外键引用父表中的唯一行；不能有重复。
Re your comment:
回复您的评论：
If T.Ais 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.
如果T.A是主键，那么不，您不能有任何重复项。任何主键都必须是唯一且非空的。因此，如果子表有一个引用父表主键的外键，它必须匹配一个非空的唯一值，因此正好引用父表中的一行。在这种情况下，您不能创建引用多个父行的子行。
You cancreate a child row whose foreign key column is NULL, in which case it references no row in the parent table.
您可以创建一个外键列为 NULL 的子行，在这种情况下，它不引用父表中的任何行。
回答by Oded
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 keyentry:
维基百科对外键条目有以下定义：
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.
候选键在表中是唯一的。
回答by Jon Heller
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.)
如果主键使用非唯一索引并且在创建时未验证，则可能会发生这种情况。（但我在现实生活中从未见过这样的情况。正如@Bill Karwin 指出的那样，这会非常令人困惑。所以这可能不是您真正需要担心的情况。）
--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);
