-- 因為使用星空大,ckp6250大,還有眾版友使用MySQL的較多,就用MySQL來討論吧.
-- 建立兩個table, 並有外鍵關聯.
create table m1 (
c1 int
, c2 int
, c3 int
, unique (c1, c2, c3)
);
create table m2 (
c1 int
, c2 int
, c3 int
, foreign key (c1, c2, c3) references m1 (c1, c2, c3)
);
-- 然後insert
insert into m2 values (1, 1, 1);
insert into m2 values (null, 1, 1);
insert into m2 values (1, null, 1);
insert into m2 values (1, 1, null);
insert into m2 values (null, null, null);
-- 接著 query m2
select *
from m2;
-- 將會顯示什麼? 並解釋原因.
-- 一起來玩吧.
已邀請的邦友 {{ invite_list.length }}/5
插入資料時，外來鍵欄位的值,必需已經存在參考資料表的來源欄位裡。
因此在insert into m2 values (1, 1, 1);的時候就會出錯
Cannot add or update a child row: a foreign key constraint fails (`test`.`m2`, CONSTRAINT `m2_ibfk_1` FOREIGN KEY (`c1`, `c2`, `c3`) REFERENCES `m1` (`c1`, `c2`, `c3`))
然而外來鍵若有欄位為NULL,表示在父資料表裡沒有相符的主鍵。
因此若將insert into m2 values (1, 1, 1)拿掉，皆可正常新增
m1沒有一筆資料的c1,c2,c3是 1,1,1
insert into m2 values (1, 1, 1) 觸發了外來鍵的限制：referential integrity
然而外來鍵若有欄位為NULL,表示在父資料表裡沒有相符的主鍵。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
你再想想看.
A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.
我記得是這個特性導致1,1,1以外的insert可以正常執行
我很少用這樣的關聯。
不過依照我個人的看法。
問題因該只是在於有無觸發關聯特性。
所以關聯的指向是針對c1 c2 c3。同時有值的情況下，才會觸發。
而包含null並不會被觸發關聯。所以可以正常的插入資料。
不過這倒是很有趣的現象。
其實三個有值會出錯我不會意外。
但有包含null就可以儲存，倒是讓我覺得有點得有趣。
不過依照我以前用過null大法，用在count上時。
感覺也可以用同樣的理由來說明。
因為null導致單純。
不會這樣用的，因為這樣算是一種破壞的行為。
不過我想你第一次參加「趣味SQL」吧。
我跟一級屠豬士，還有其它人，會出一些不是常理使用，但研究理論的題目。
在常理上的確不會使用null來做外鍵關聯。
但可以研究其特性及原理。
這是用來探討,可以有更深入的認識.
其實這還有一個延伸的應用。用在count上。
依照上面的例子。先排除1,1,1不能加入的值。用其它4個值
insert into m2 values (null, 1, 1);
insert into m2 values (1, null, 1);
insert into m2 values (1, 1, null);
insert into m2 values (null, null, null);
如果下
SELECT c1,count(*) FROM `m2` GROUP BY c1
會出現
c1 count(*)
NULL 2
1 2
但如下是換成
SELECT c1,count(c1) FROM `m2` GROUP BY c1
則會變成
c1 count(*)
NULL 0
1 2
以上很奇特的現象。
我記得不知道在哪邊看到的是，mysql會將屬於null的資料。
依無效論定。
也就是說，用同樣的道理用在關聯上。
關聯的特性會因為無效論定而不會有作用。
導致可以插入無法關聯的資料進去。
首先因為外鍵不受null影響
也就是null可以過關(但是我找不到官方文件說明xd..)
再來是因為新增外鍵的時候
, foreign key (c1, c2, c3) references m1 (c1, c2, c3)
把c1, c2, c3綁在一起了
所以其實三個一起算才是唯一(類似index(a), index(b)跟 index(a, b)不同)
又因為有null不判斷,因此除了1,1,1不能之外其他都能過關
應該是複合外鍵只要其中一個為null就會跳過整個外鍵判斷
還蠻特別的 哈哈
被點名了，趕快來打個卡。
實在擠不出料來，只好認真打幾個字交卷。
null 這個東東，非人非鬼，「跳出三界外，不在五行中」，任何東東踫到它，都會被同化。
比如 select 1+1 ...... = 2
但 select 1+null ... = null
又
select 1=1 ........ true
select 1=2 ........ false
但 select null=null ......既不是 true , 也不是 false , 答案還是 null
可見 null 神通廣大，高深莫測，外鍵限制根本不知道該算它對還是錯，只好放行了。
順便帶一句，我倒很喜歡在欄位上設置 null 屬性，但我的工程師很害怕 null , 他所有的欄位一定設成 not null , 他覺得很不好掌握，下 where 時經常給我出槌。
因為我不怕 null , 他怕 null , 所以我當老闆，他只能當夥計。
亂扯一通，殺豬大，別 K 我。
iThome鐵人賽
看影片追技術