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