一直认为在oracle中,外键是不能为空的,今天突然发现错了,所以做了个实验:
----创建表
SQL> create table t(id number,name varchar2(4),sex char(2),constraint pk_id_name primary key(id,name));
Table created.
SQL> create table tt(id number,name varchar2(4),class varchar2(2),constraint fk_id_name foreign key(id,name) references t(id,name));
Table created.
----插入数据至t
SQL> insert into t values (1,'a','m');
1 row created.
SQL> insert into t values (2,'b','m');
1 row created.
SQL> insert into t values (3,'c','f');
1 row created.
----测试
SQL> insert into tt values (null,null,'01');
1 row created.
--以上一行可以看出当外键都为null时是可以插入的
SQL> insert into tt values (1,null,'01');
1 row created.
SQL> insert into tt values (null,'a','01');
1 row created.
SQL> insert into tt values (null,'d','01');----t表name列无'd'
1 row created.
SQL> insert into tt values (5,null,'01');----t表id列无'5'
1 row created.
--以上四行可以看出当组合外键中有一列为空时,另一列可以不参照t表的相应数据
SQL> insert into tt values (5,'d','01');
insert into tt values (5,'d','01')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ID_NAME) violated - parent key not
found
所以外键约束是可以为空的,当为组合外键约束时只要其中有一列为空,则会跳过约束的检查,即别的列可以为任何值。
-----------------------------------------------------------------------------------------------------
ps:外键不仅可以和主键相关联,也可以是unique约束相关联