iT邦幫忙

3

趣味SQL 又來了! 來玩外鍵限制,猜測行為與解釋原因

-- 因為使用星空大,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;
  
-- 將會顯示什麼? 並解釋原因.
-- 一起來玩吧.
1
通靈亡
iT邦研究生 1 級 ‧ 2020-06-08 23:48:22

插入資料時,外來鍵欄位的值,必需已經存在參考資料表的來源欄位裡。
因此在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,表示在父資料表裡沒有相符的主鍵。

  1. 一個外來鍵可包含多個欄位
  2. 外來鍵其中一個欄位是允許NULL,則整個外來鍵允許NULL
  3. 當外來鍵包含多個欄位,只要外來鍵其中一個欄位是NULL,整個外來鍵會視為NULL

因此若將insert into m2 values (1, 1, 1)拿掉,皆可正常新增

看更多先前的回應...收起先前的回應...

那是外鍵限制生效了, 後續還有啊~~~~

通靈亡 iT邦研究生 1 級 ‧ 2020-06-08 23:56:40 檢舉

m1沒有一筆資料的c1,c2,c3是 1,1,1
insert into m2 values (1, 1, 1) 觸發了外來鍵的限制:referential integrity

然而外來鍵若有欄位為NULL,表示在父資料表裡沒有相符的主鍵。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
你再想想看.

通靈亡 iT邦研究生 1 級 ‧ 2020-06-09 00:16:24 檢舉

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可以正常執行

2

我很少用這樣的關聯。

不過依照我個人的看法。
問題因該只是在於有無觸發關聯特性。

所以關聯的指向是針對c1 c2 c3。同時有值的情況下,才會觸發。
而包含null並不會被觸發關聯。所以可以正常的插入資料。

不過這倒是很有趣的現象。
其實三個有值會出錯我不會意外。

但有包含null就可以儲存,倒是讓我覺得有點得有趣。
不過依照我以前用過null大法,用在count上時。
感覺也可以用同樣的理由來說明。

因為null導致單純。

看更多先前的回應...收起先前的回應...
通靈亡 iT邦研究生 1 級 ‧ 2020-06-09 09:50:22 檢舉

我蠻好奇實務上
資料表設計在什麼情況下,會用到多欄位外來鍵可包含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的資料。
依無效論定。

也就是說,用同樣的道理用在關聯上。
關聯的特性會因為無效論定而不會有作用。
導致可以插入無法關聯的資料進去。

0
Victor
iT邦新手 3 級 ‧ 2020-06-09 09:44:06

m1 找不到 1,1,1 插不進去!

0
firecold
iT邦新手 2 級 ‧ 2020-06-09 15:28:29

首先因為外鍵不受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就會跳過整個外鍵判斷
還蠻特別的 哈哈

1
ckp6250
iT邦好手 1 級 ‧ 2020-06-09 20:32:12

被點名了,趕快來打個卡。
實在擠不出料來,只好認真打幾個字交卷。

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 我。/images/emoticon/emoticon02.gif

感謝老哥哥無私分享心得.

我要發表回答

立即登入回答