[20121020]主外键约束以及NULL问题.txt
主外键约束可以一定程度保证数据完整性,但是如果外键输入的是NULL,情况会如何呢?
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1.测试1:
drop table t1 purge;
drop table t2 purge;
create table t1( id number, name varchar2(10));
alter table t1 add constraint pk_t1 unique (id, name);
SQL> desc t1;
Name Null? Type
------ -------- ----------------
ID NUMBER
NAME VARCHAR2(10)
create table t2( id2 number ,id number, name varchar2(10));
alter table t2 add constraint fk_t2 foreign key(id, name) references t1(id, name);
insert into t1 values(1,'test');
commit ;
insert into t2 values(1,2,'test');
insert into t2 values(2,1,'aaaa');
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_T2) violated - parent key not found
--但是当我们插入空值时:
insert into t2 values(3,1,null);
insert into t2 values(4,2,null);
insert into t2 values(5,null,'aaaa');
insert into t2 values(6,null,'bbbb');
insert into t2 values(7,null,null);
commit ;
SQL> set NULL NULL
SQL> select * from t2;
ID2 ID NAME
---------- ---------- ----------
3 1 NULL
4 2 NULL
5 NULL aaaa
6 NULL bbbb
7 NULL NULL
2.测试2:
drop table t1 purge;
drop table t2 purge;
create table t1( id number, name varchar2(10));
alter table t1 add constraint pk_t1 primary key (id, name);
SQL> desc t1
Name Null? Type
------ -------- -------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(10)
create table t2( id2 number ,id number, name varchar2(10));
alter table t2 add constraint fk_t2 foreign key(id, name) references t1(id, name);
insert into t1 values(1,'test');
commit ;
insert into t2 values(1,2,'test');
insert into t2 values(2,1,'aaaa');
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_T2) violated - parent key not found
--但是当我们插入空值时:
insert into t2 values(3,1,null);
insert into t2 values(4,2,null);
insert into t2 values(5,null,'aaaa');
insert into t2 values(6,null,'bbbb');
insert into t2 values(7,null,null);
commit ;
SQL> set NULL NULL
SQL> select * from t2;
ID2 ID NAME
---------- ---------- ----------
3 1 NULL
4 2 NULL
5 NULL aaaa
6 NULL bbbb
7 NULL NULL
3.总结:
所以要保证完整性,还要定义外键NOT NULL.