【SQL Server】削除時にNULLを設定する外部キー（ON DELETE SET NULL）の作成方法

本記事では、SQL Serverにおける「ON DELETE SET NULL」制約付きの外部キー（FOREIGN KEY）について、その動作の仕組みと、CREATE TABLE文・ALTER TABLE文を使った具体的な作成方法を、サンプルコード付きでわかりやすく解説します。

ON DELETE SET NULL付き外部キーとは？

「ON DELETE SET NULL」を指定した外部キーでは、親テーブルのレコードが削除されると、子テーブル側の対応するレコードの外部キー列の値が自動的にNULLに更新されます。このとき、子テーブルのレコード自体は削除されず、SQL Server上にそのまま残る点が大きな特徴です。

削除時にNULLを設定するタイプの外部キーは、CREATE TABLE文またはALTER TABLE文のいずれかを使って作成できます。

CREATE TABLE文でON DELETE SET NULLの外部キーを作成する

構文

CREATE TABLE 子テーブル名
(
  列1 データ型 [ NULL | NOT NULL ],
  列2 データ型 [ NULL | NOT NULL ],
  …

  CONSTRAINT 制約名
    FOREIGN KEY (子列1, 子列2, … 子列n)
    REFERENCES 親テーブル名 (親列1, 親列2, … 親列n)
    ON DELETE SET NULL
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

各パラメータの意味

子テーブル名
新しく作成する子テーブルの名前です。

列1、列2…
テーブルに作成する列です。各列には1つのデータ型を指定し、NULLを許可するかどうか（NULL / NOT NULL）も指定できます。省略した場合はNULLが許可されます。

制約名
作成する外部キー制約の名前です。

子列1〜子列n
子テーブル側で、親テーブルの主キーを参照する列です。

親テーブル名
子テーブルから参照される主キーを持つ、親テーブルの名前です。

親列1〜親列n
親テーブルの主キーを構成する列です。外部キーによって、子テーブルの子列との間に参照整合性の制約が張られます。

ON DELETE SET NULL
親テーブルのデータが削除されたときに、子テーブルの対応するデータの値をNULLに設定します。子テーブルのデータ自体は削除されません。

ON UPDATE（オプション）
親テーブルのデータが更新されたときに、子テーブル側でどのような処理を行うかを指定します。指定できるのは以下の4種類です。

• NO ACTION：親データが削除・更新されても、子データには何もしません。
• CASCADE：親データが削除・更新されると、子データも一緒に削除・更新されます。
• SET NULL：親データが削除・更新されると、子データの値がNULLに設定されます。
• SET DEFAULT：親データが削除・更新されると、子データの値が既定値（デフォルト値）に設定されます。

使用例

CREATE TABLE shouhin
( shouhin_id INT PRIMARY KEY,
  shouhin_mei VARCHAR(50) NOT NULL,
  bunrui VARCHAR(25)
);

CREATE TABLE zaiko
( zaiko_id INT PRIMARY KEY,
  shouhin_id INT,
  suuryou INT,
  saishou_ryou INT,
  saidai_ryou INT,
  CONSTRAINT fk_zaiko_shouhin_id
    FOREIGN KEY (shouhin_id)
    REFERENCES shouhin (shouhin_id)
    ON DELETE SET NULL
);

この例では、まず主キーとしてshouhin_id（商品ID）を持つ親テーブル「shouhin（商品）」を作成しています。続いて、削除時SET NULL制約を持つ外部キーを含む子テーブル「zaiko（在庫）」を作成しました。CREATE TABLE文により、zaikoテーブルにはfk_zaiko_shouhin_idという名前の外部キーが定義され、zaikoテーブルのshouhin_id列とshouhinテーブルのshouhin_id列との間に関連付けが作られます。

この外部キーにはON DELETE SET NULLが指定されているため、親テーブルのデータが削除されると、子テーブル側の対応するレコードの値がNULLに設定されます。具体的には、shouhinテーブルからあるshouhin_idが削除されると、同じshouhin_idを参照していたzaikoテーブルのレコードのshouhin_id列がNULLになります。

注意点：外部キー列はNULLを許可する必要がある

ON DELETE SET NULLを使う場合、子テーブルの外部キー列がNULL値を受け入れられるようにしておく必要があります。もしCREATE TABLE文でこの列をNOT NULLにしてしまうと、次のようなエラーが発生します。

CREATE TABLE zaiko
( zaiko_id INT PRIMARY KEY,
  shouhin_id INT NOT NULL,
  suuryou INT,
  saishou_ryou INT,
  saidai_ryou INT,
  CONSTRAINT fk_zaiko_shouhin_id
    FOREIGN KEY (shouhin_id)
    REFERENCES shouhin (shouhin_id)
    ON DELETE SET NULL
);

Msg 1761, Level 16, State 0, Line 1
Cannot create the foreign key 'fk_zaiko_shouhin_id' with the SET NULL referential action, because one or more referencing columns are not nullable.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

エラーメッセージの内容は、「SET NULLの参照操作を持つ外部キーは、NULLを許容しない列が存在するため作成できない」というものです。これを回避するには、下のようにzaikoテーブルのshouhin_id列がNULLを許可するように定義します。

CREATE TABLE zaiko
( zaiko_id INT PRIMARY KEY,
  shouhin_id INT,
  suuryou INT,
  saishou_ryou INT,
  saidai_ryou INT,
  CONSTRAINT fk_zaiko_shouhin_id
    FOREIGN KEY (shouhin_id)
    REFERENCES shouhin (shouhin_id)
    ON DELETE SET NULL
);

Commands completed successfully.

ALTER TABLE文でON DELETE SET NULLの外部キーを作成する

すでに存在するテーブルに対して後から外部キーを追加したい場合は、ALTER TABLE文を使用します。

構文

ALTER TABLE 子テーブル名
ADD CONSTRAINT 制約名
  FOREIGN KEY (子列1, 子列2, … 子列n)
  REFERENCES 親テーブル名 (親列1, 親列2, … 親列n)
  ON DELETE SET NULL;

各パラメータの意味はCREATE TABLE文の場合と同様です。ON DELETE SET NULLを指定すると、親テーブルのデータが削除された際に、子テーブルの該当データがNULLに設定され、子テーブルのデータ自体は削除されません。

使用例

ALTER TABLE zaiko
ADD CONSTRAINT fk_zaiko_shouhin_id
  FOREIGN KEY (shouhin_id)
  REFERENCES shouhin (shouhin_id)
  ON DELETE SET NULL;

この例では、既存のzaikoテーブルに対して、fk_zaiko_shouhin_idという名前の外部キーを追加し、親テーブルshouhinのshouhin_id列を参照させています。

ON DELETE SET NULLが指定されているため、親テーブルのshouhin_idのデータが削除されると、zaikoテーブル内の該当するレコードのshouhin_id列にはNULLが設定されます。

まとめ

ON DELETE SET NULLは、親レコードの削除時に子レコードを残しつつ、参照関係だけを解除できる便利な参照整合性オプションです。ただし、対象となる外部キー列がNULLを許可している必要がある点には注意しましょう。テーブル作成時に定義するならCREATE TABLE文、既存テーブルに後から追加するならALTER TABLE文と、用途に応じて使い分けるのがおすすめです。