UNION ALL を使用して2つの候補テーブルを1つの仮想テーブルにまとめる

2024-04-03

MySQLで外部キーを2つの候補テーブルのいずれかに設定する方法

MySQLで、1つの列が2つの候補テーブルのいずれかのレコードを参照する必要がある外部キーを設定したい場合があります。

解決策：

MySQLでは、直接的に2つのテーブルに外部キーを設定することはできません。しかし、以下のいずれかの方法で実現できます。

UNION ALL を使用した単一テーブル:

2つの候補テーブルを UNION ALL で結合して単一の仮想テーブルを作成します。
外部キーを仮想テーブルの該当列に設定します。

例:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES (customer_id) IN customers
);

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES (product_id) IN products
);

CREATE VIEW all_customers_suppliers AS
SELECT * FROM customers
UNION ALL
SELECT * FROM suppliers;

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES all_customers_suppliers (customer_id);

メリット:

シンプルでわかりやすい

仮想テーブルの更新や削除が複雑になる

CASE 式を使用したトリガー:

INSERT や UPDATE トリガーを作成し、CASE 式を使用して、挿入または更新されるレコードがどのテーブルを参照するかを判断します。
該当するテーブルの外部キー制約に基づいて、レコードを挿入または更新します。

DELIMITER //

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.customer_id IS NOT NULL THEN
    SET NEW.customer_id = (
      SELECT customer_id
      FROM customers
      WHERE customer_id = NEW.customer_id
    );
  ELSE
    SET NEW.supplier_id = (
      SELECT supplier_id
      FROM suppliers
      WHERE supplier_id = NEW.supplier_id
    );
  END IF;
END;
//

DELIMITER ;

柔軟性が高い

トリガーの理解と管理が複雑になる

エンティティフレームワークのような ORM ツールを使用すると、外部キーの設定をコードレベルで行うことができます。ツールによって方法は異なりますが、一般的には、関連付けのプロパティを設定することで実現できます。

コードレベルで外部キーを設定できる
開発効率が向上する

ORM ツールの知識が必要

上記以外にも、アプリケーションロジックを使用して外部キー制約を実装する方法もあります。
外部キーを設定する前に、データの整合性を確認する必要があります。

UNION ALL を使用した単一テーブル

-- テーブル作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  supplier_id INT
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  product_id INT
);

-- 仮想テーブル作成
CREATE VIEW all_customers_suppliers AS
SELECT customer_id, NULL AS supplier_id FROM customers
UNION ALL
SELECT NULL AS customer_id, supplier_id FROM suppliers;

-- 外部キー設定
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES all_customers_suppliers (customer_id),
ADD FOREIGN KEY (supplier_id) REFERENCES all_customers_suppliers (supplier_id);

-- データ挿入
INSERT INTO orders (customer_id, supplier_id) VALUES (1, NULL);
INSERT INTO orders (customer_id, supplier_id) VALUES (NULL, 2);

-- 確認
SELECT * FROM orders;

-- 結果
-- order_id | customer_id | supplier_id
-- -------- | -------- | --------
-- 1        | 1          | NULL
-- 2        | NULL       | 2

CASE 式を使用したトリガー

-- テーブル作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  supplier_id INT
);

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  product_id INT
);

-- トリガー作成
DELIMITER //

CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  IF NEW.customer_id IS NOT NULL THEN
    SET NEW.customer_id = (
      SELECT customer_id
      FROM customers
      WHERE customer_id = NEW.customer_id
    );
  ELSE
    SET NEW.supplier_id = (
      SELECT supplier_id
      FROM suppliers
      WHERE supplier_id = NEW.supplier_id
    );
  END IF;
END;
//

DELIMITER ;

-- データ挿入
INSERT INTO orders (customer_id, supplier_id) VALUES (1, NULL);
INSERT INTO orders (customer_id, supplier_id) VALUES (NULL, 2);

-- 確認
SELECT * FROM orders;

-- 結果
-- order_id | customer_id | supplier_id
-- -------- | -------- | --------
-- 1        | 1          | NULL
-- 2        | NULL       | 2

エンティティフレームワーク

MySQLで外部キーを2つの候補テーブルのいずれかに設定する他の方法

サブクエリを使用した外部キー制約:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES (customer_id) IN (
    SELECT customer_id
    FROM customers
  )
);

CREATE TABLE suppliers (
  supplier_id INT PRIMARY KEY,
  product_id INT,
  FOREIGN KEY (supplier_id) REFERENCES (supplier_id) IN (
    SELECT supplier_id
    FROM suppliers
  )
);

サブクエリが実行されるたびにパフォーマンスが低下する

CHECK 制約:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  supplier_id INT,
  CHECK (customer_id IS NOT NULL OR supplier_id IS NOT NULL),
  CHECK (
    (customer_id IS NOT NULL AND EXISTS (SELECT * FROM customers WHERE customer_id = orders.customer_id))
    OR
    (supplier_id IS NOT NULL AND EXISTS (SELECT * FROM suppliers WHERE supplier_id = orders.supplier_id))
  )
);