MySQL 外部キー制約と NULL 値許可：データ整合性を保ちながら柔軟なデータ構造を実現

2024-04-03

MySQL 外部キーと NULL 値許可について

概要

許可設定

MySQL 8.0 以降では、FOREIGN KEY 制約時に NULL 値を許可するかどうかを明示的に指定できます。

NULLS ALLOWED: 子テーブルの列に NULL 値を許可します。
NOT NULL: 子テーブルの列に NULL 値を許可しません。(デフォルト)

例：

CREATE TABLE child (
  id INT NOT NULL AUTO_INCREMENT,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent (id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
    **NULLS ALLOWED**
);

上記の例では、child テーブルの parent_id 列は NULL 値を許可します。

使用例

子テーブルに存在しない親レコードへの参照

子テーブルのレコードが常に親テーブルのレコードを参照するとは限りません。例えば、以下のケースでは NULL 値が有効です。

子テーブルに新規レコードを作成する前に、親レコードがまだ作成されていない場合
親レコードが削除された後、子レコードはまだ存在している場合

子テーブルの一部レコードのみ親レコードを参照

すべてのレコードが親レコードを参照する必要がない場合、NULL 値を許可することでテーブル構造を簡潔に保てます。

注意点

NULL 値を許可すると、データ整合性のリスクが高くなります。
外部キー制約と NULL 値の組み合わせは、複雑なロジックやパフォーマンスの問題を引き起こす可能性があります。

MySQL 外部キー制約と NULL 値の許可は、データ構造とロジックの要件に応じて慎重に検討する必要があります。

NULLS ALLOWED を使用した外部キー制約

CREATE TABLE parent (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE child (
  id INT NOT NULL AUTO_INCREMENT,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES parent (id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
    **NULLS ALLOWED**
);

NULL 値を含むレコードの挿入

INSERT INTO parent (name) VALUES ('親レコード1');
INSERT INTO child (parent_id) VALUES (1);

INSERT INTO child (parent_id) VALUES (NULL); -- 親レコードが存在しない場合

親レコードの削除

DELETE FROM parent WHERE id = 1;

-- 子レコードの `parent_id` は `NULL` に設定される
SELECT * FROM child;

外部キー制約違反

-- `parent_id` が存在しないレコードを挿入しようとするとエラーが発生
INSERT INTO child (parent_id) VALUES (999);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))