[20121020]主外键约束以及NULL问题.txt

[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.