MySQLの外部キー制約：子テーブルの外部キー項目にNULLのinsertの可否について。

Question

MｙSQLの外部キー制約についてです。

外部キー制約のつけた列に、制約の例外としてNULL値を許容したいと思っています。
方法はありますでしょうか。

■行った事。
外部キー制約のつけた列に既にNULLlが既に入っている場合、制約をつけることは出来ました。
しかし、制約をつけた後、その子テーブルの外部キー制約を張った列にNULLをinsertしようとすると、外部キー制約違反と叱られます。

■自分の考えた代替案
nullに変わる意味をもつ値を親側の表に持たせる。例えば、親側に、primary key が0で、項目名が【なし】など。

外部キー制約はどうしてもつけたいとき、その外部キーフィールド（子テーブル側）にnull値のinsertを許容する方法はないのでしょうか。
※参考：親側はテーブルは、追加されることはあっても削除・変更されることは、運用上ありません。

どうぞ、よろしくお願いします。

Answer 1

create table parent_tbl(pid int primary key,pval int);
create table child_tbl(cid int primary key,cval int,pid int null,index(pid),foreign key(pid) references parent_tbl(pid) on delete set null on update cascade);
insert into parent_tbl values
(1,1000),
(2,1001),
(3,1002);
insert into child_tbl values
(1,2000,1),
(2,2001,1),
(3,2002,2),
(4,2003,3),
(5,2004,null);

として

delete from parent_tbl where pid=3;

とするとchild_tblのcid=4のpidがnullになります
また

update parent_tbl set pid=5 where pid=2;

とするとchild_tblのcid=3のpidが5になります

Answer 2

外部キー制約のつけた列に既にNULLlが既に入っている場合、制約をつけることは出来ました。

しかし、制約をつけた後、その子テーブルの外部キー制約を張った列にNULLをinsertしようとすると、外部キー制約違反と叱られます。

外部キーは親テーブルにキーが登録されているかチェックする機能ですから、当然怒られます。
小テーブルに外部キーは設定できませんから、小テーブルのキーにNULL以外が設定されたら親テーブルに存在チェックを入れてください。

Comments

[tt02]

明快なご回答ありがとうございます。
つまり、nullを入れたい場合は、外部キー制約を使うのではなく、
プログラムによるチェックということですね。

[Orlofsky]

そうです。

実務である程度大きなシステムになると外部キーはほとんど使いません。
外部キーを使うことで遅くなるし、外部キーを使わないでもデータの整合性を取れるレベルのプログラママーを用意します。

[tt02]

大変すっきりしました！！