SQL Server - NULLを含んだ複合外部キーのメモ
複合外部キーを使うことになったんですが、外部キー制約を設定するカラムの1つの値がNULLの場合に想像していた動きと違ったのでメモを残しておきます。(想像と違うのはわたしだけかもですが)
次のようなテーブルがあったとします。SampleBに外部キー制約があります。
-- 参照されるテーブル create table SampleA( Id int identity not null, -- Key1とKey2の2つのカラムが外部キー制約で参照される Key1 int not null, Key2 int not null, constraint PK_SampleA primary key(Id), -- 外部キー制約で参照されるためにユニーク制約を設定 constraint UQ_SampleA unique(Key1, Key2) ); -- 参照する(外部キーがある)テーブル create table SampleB( Id int identity not null, Key1 int null, -- NULLを許可 Key2 int null, -- NULlを許可 constraint PK_SampleB primary key(Id), -- SampleAのKey1、Key2に対して複合外部キー制約を設定 constraint FK_SampleB_SampleA foreign key(Key1, Key2) references SampleA(Key1, Key2) ); -- 参照されるSampleAにデータを投入 insert into SampleA(Key1, Key2) output inserted.* values (1, 2), (1, 3); /* Id Key1 Key2 ----------- ----------- ----------- 1 1 2 2 1 3 */
この状態でSampleBのテーブルにデータを投入してみます。
まずは普通に外部キーから参照されるSampleAの列に値が存在する場合、当然ですがINSERTできます。
insert into SampleB(Key1, Key2) output inserted.* values(1, 2); /* Id Key1 Key2 ----------- ----------- ----------- 1 1 2 */
参照される列に値が存在しない場合は、INSERTに失敗します。
-- これはエラーになる insert into SampleB(Key1, Key2) values(1, 4); -- INSERT ステートメントは FOREIGN KEY 制約 "FK_SampleB_SampleA" と競合しています。 -- 競合が発生したのは、データベース "Test"、テーブル "dbo.SampleA" です。
外部キーのどちらかがNULLの場合や両方がNULLの場合、たとえば次のようなデータはINSERTできます。
insert into SampleB(Key1, Key2) output inserted.* values (1, null), (null, 2), (3, null), (null, null); /* Id Key1 Key2 ----------- ----------- ----------- 3 1 NULL 4 NULL 2 5 3 NULL 6 NULL NULL */
外部キーのどちらがNULLの場合はINSERTに失敗するのかなと思っていたのですが・・・。
MSDNにこのあたりの動きの記載がありましたので引用しておきます。
複合外部キー制約のすべての値が検証されるようにするには、参加しているすべての列に NOT NULL を指定します。
NULLがあると複合外部キー制約が検証されないってことですね。