[20160704]NULL与主外键问题.txt

--主外键的问题主要出现在阻塞等情况,有许多极端dba认为应该取消这个约束.当然从使用的角度出发有总比没有好.只是不要过度滥用.
--我这里遇到一个问题,开发指出主外键"失效",我仔细检查发现建立的主表索引是唯一,而非主键,这样可以插入NULL,导致所谓的"失效".
--我开始意味是因为oracle索引不记录NULL而导致的(主键仅仅一个字段).实际上我的测试还是有点意外:

1.环境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--drop table cheap purge;
--drop table pheap purge;

create table pheap (
  id number(10) ,
  v varchar2(10)
);

create unique index pk_pheap on scott.pheap (id,v);
alter table pheap add constraint uk_pheap unique (id,v);

create table cheap (
  id number(10) primary key,
  p_id number(10) ,
  v varchar2(10)
);

alter table cheap add constraint fk_cheap foreign key (p_id,v) references pheap (id,v);
--//注意如果前面没有alter table pheap add constraint uk_pheap unique (id,v);
--//SCOTT@book> alter table cheap add constraint fk_cheap foreign key (p_id,v) references pheap (id,v);
--//alter table cheap add constraint fk_cheap foreign key (p_id,v) references pheap (id,v)
--//                                                                                 *
--//ERROR at line 1:
--//ORA-02270: no matching unique or primary key for this column-list

SCOTT@book> @ &r/desc pheap;
Name  Null?    Type
----- -------- ----------------------------
ID             NUMBER(10)
V              VARCHAR2(10)

SCOTT@book> @ &r/desc cheap;
Name   Null?    Type
------ -------- ----------------------------
ID     NOT NULL NUMBER(10)
P_ID            NUMBER(10)
V               VARCHAR2(10)
--都是NULL.

insert into pheap values (1, '1');
insert into pheap values (2, '2');
insert into cheap values (1, 1, '1');
commit;

create index cheappid on cheap (p_id,v);

2.测试:
SCOTT@book> select * from pheap;
        ID V
---------- ----------
         1 1
         2 2

SCOTT@book> select * from cheap;
        ID       P_ID V
---------- ---------- ----------
         1          1 1

SCOTT@book> insert into cheap values (2, 2, NULL);
1 row created.

--//可以发现我插入insert into cheap values (2, 2, NULL);在phead并不存在(2,NULL)的记录.

SCOTT@book> insert into cheap values (3, 3, NULL);
1 row created.

--//一样ok.
SCOTT@book> insert into cheap values (4, null, 'a');
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from pheap;
ID V
--- ----------
  1 1
  2 2

SCOTT@book> select * from cheap;
ID       P_ID V
--- ---------- ----------
  1          1 1
  2          2
  3          3
  4            a

--如果插入:
SCOTT@book> insert into cheap values (4, 5, '5');
insert into cheap values (4, 5, '5')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0012621) violated

SCOTT@book> delete from pheap where id=2;
1 row deleted.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> insert into pheap values (2, NULL);
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> delete from pheap where id=2;
1 row deleted.

--完全不会影响cheap的记录.

SCOTT@book> rollback;
Rollback complete.

SCOTT@book> delete from pheap where id=1;
delete from pheap where id=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_CHEAP) violated - child record found

--当然我的测试非常特殊,主表没有建立主键索引.从另外一个侧面索引数据库设计非常重要.