SQL 外部キーと NULL に関するベストプラクティス

外部キーを持つテーブル列が NULL にできるかどうか

SQL、MySQL、データベースにおける、外部キーを持つテーブル列が NULL にできるかどうかについて解説します。

外部キーを持つテーブル列は、状況によって NULL を許可できます。

詳細

外部キー制約は、子テーブルの列と親テーブルの列を関連付けるデータベースの制約です。この制約により、子テーブルの各行は、親テーブルの既存の行を参照する必要があります。

NULL 許容

外部キー列が NULL を許可するかどうかは、以下の条件によって決まります。

• 親テーブルの列が NULL を許可するかどうか: 親テーブルの列が NULL を許可する場合、子テーブルの外部キー列も NULL を許可できます。
• 参照整合性の要件: 子テーブルの行が親テーブルの行を参照していない場合、参照整合性が失われます。そのため、参照整合性を維持するために、外部キー列が NULL を許可しない場合があります。

例

• 従業員テーブルと部門テーブル: 従業員テーブルには、部門テーブルの部門IDを外部キーとして持つ列があります。部門テーブルの部門ID列が NULL を許可しない場合、従業員テーブルの外部キー列も NULL を許可できません。これは、従業員が所属していない部門が存在しないためです。

設定方法

MySQL の例:

CREATE TABLE 子テーブル (
  子テーブルID INT PRIMARY KEY,
  親テーブルID INT,
  FOREIGN KEY (親テーブルID) REFERENCES 親テーブル (親テーブルID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

上記の例では、子テーブル の 親テーブルID 列は外部キーであり、親テーブル の 親テーブルID 列を参照します。ON DELETE CASCADE オプションは、親テーブルの行が削除された場合、子テーブルの関連する行も自動的に削除することを指定します。ON UPDATE CASCADE オプションは、親テーブルの行が更新された場合、子テーブルの関連する行も自動的に更新することを指定します。

NULL 許容の利点と欠点

利点

• データの冗長性を減少させることができます。

欠点

• 参照整合性が失われる可能性があります。
• データの解釈が複雑になる可能性があります。

外部キーを持つテーブル列が NULL を許可するかどうかは、状況に応じて慎重に判断する必要があります。

-- 親テーブル
CREATE TABLE 親テーブル (
  親テーブルID INT PRIMARY KEY,
  親テーブル列1 VARCHAR(255)
);

-- 子テーブル
CREATE TABLE 子テーブル (
  子テーブルID INT PRIMARY KEY,
  親テーブルID INT,
  子テーブル列1 VARCHAR(255),
  FOREIGN KEY (親テーブルID) REFERENCES 親テーブル (親テーブルID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- データ挿入
INSERT INTO 親テーブル (親テーブル列1) VALUES ('親テーブルデータ1');
INSERT INTO 親テーブル (親テーブル列1) VALUES ('親テーブルデータ2');

INSERT INTO 子テーブル (親テーブルID, 子テーブル列1) VALUES (1, '子テーブルデータ1');
INSERT INTO 子テーブル (親テーブルID, 子テーブル列1) VALUES (2, '子テーブルデータ2');

-- 外部キー制約違反
INSERT INTO 子テーブル (親テーブルID, 子テーブル列1) VALUES (3, '子テーブルデータ3');

-- 親テーブルのデータ削除
DELETE FROM 親テーブル WHERE 親テーブルID = 2;

-- 子テーブルの関連データも自動的に削除
SELECT * FROM 子テーブル;

-- 親テーブルのデータ更新
UPDATE 親テーブル SET 親テーブル列1 = '更新後データ' WHERE 親テーブルID = 1;

-- 子テーブルの関連データも自動的に更新
SELECT * FROM 子テーブル;

実行結果

-- 外部キー制約違反
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`子テーブル`, CONSTRAINT `子テーブル_ibfk_1` FOREIGN KEY (`親テーブルID`) REFERENCES `親テーブル` (`親テーブルID`))

-- 子テーブルの関連データも自動的に削除
Empty set (0.00 sec)

-- 子テーブルの関連データも自動的に更新
親テーブルID | 親テーブル列1 | 子テーブル列1
-------------- | -------------- | --------------
1             | 更新後データ     | 子テーブルデータ1

解説

上記のサンプルコードでは、以下の処理を行っています。

1. 親テーブルと子テーブルを作成します。
2. 外部キー制約違反が発生することを確認します。
3. 親テーブルのデータ削除と更新を行い、子テーブルの関連データも自動的に削除・更新されることを確認します。

注意事項

• 上記のサンプルコードは、MySQL 8.0 を使用しています。
• 外部キー制約を設定する際は、参照整合性を考慮する必要があります。

外部キーを持つテーブル列を NULL にするその他の方法

CHECK 制約を使用して、列の値が特定の条件を満たしていることを確認できます。

CREATE TABLE 子テーブル (
  子テーブルID INT PRIMARY KEY,
  親テーブルID INT,
  子テーブル列1 VARCHAR(255),
  CHECK (親テーブルID IS NULL OR EXISTS (SELECT * FROM 親テーブル WHERE 親テーブルID = 子テーブル.親テーブルID))
);

上記の例では、子テーブル の 親テーブルID 列が NULL または親テーブルに存在する値であることを確認しています。

DEFAULT 値を使用して、列のデフォルト値を設定できます。

CREATE TABLE 子テーブル (
  子テーブルID INT PRIMARY KEY,
  親テーブルID INT DEFAULT NULL,
  子テーブル列1 VARCHAR(255)
);

上記の例では、子テーブル の 親テーブルID 列のデフォルト値を NULL に設定しています。

NULL 許容列を使用して、列が NULL を許可することを指定できます。

CREATE TABLE 子テーブル (
  子テーブルID INT PRIMARY KEY,
  親テーブルID INT,
  子テーブル列1 VARCHAR(255)
);

ALTER TABLE 子テーブル ALTER COLUMN 親テーブルID SET NULL;

各方法の比較