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があると複合外部キー制約が検証されないってことですね。