在子表中,外键(单一列)引用的父键是否可能存在一些重复值?
是的,外键可以引用具有重复值的列。
如果主键使用非唯一索引并且在创建时未进行验证,则可能会发生这种情况。(但我从未在现实生活中见过这样的情况。如@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);