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