[20160704]NULL與主外來鍵問題.txt

[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

--當然我的測試非常特殊,主表沒有建立主鍵索引.從另外一個側面索引資料庫設計非常重要.