データベース設計の落とし穴！MySQL/MariaDBで外部キーを2回使う際に知っておくべきこと

2024-06-17

MySQL/MariaDBで同じ外部キーを1つのテーブルで2回使用する

しかし、稀に同じ外部キーを2回使用したい状況が発生することがあります。例えば、あるエンティティが別のエンティティと1対多または多対多の関係を持ち、かつ、その関係が2つの異なる属性で表現される場合などが考えられます。

このような状況では、以下の代替案を検討することができます。

中間テーブルを作成し、2つのエンティティ間の関係を表現します。中間テーブルには、それぞれのエンティティへの外部キー制約と、関係を表現する属性を含めます。

例:

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

この例では、ordersという中間テーブルを作成し、customersテーブルとproductsテーブル間の多対多関係を表現しています。ordersテーブルには、それぞれのエンティティへの外部キー制約 (customer_id と product_id) と、注文個数などの関係を表現する属性を含めることができます。

複合外部キーを使用する

複数の列で構成される複合外部キー制約を使用します。これにより、同じ外部キーテーブルを参照する複数の関係を表現することができます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id, product_id, order_date)
    REFERENCES orders_details(customer_id, product_id, order_date)
);

この例では、ordersテーブル内に複合外部キー制約 (customer_id, product_id, order_date) を定義しています。この外部キー制約は、orders_detailsテーブルを参照し、注文の詳細情報を表現します。

EAV (Entity-Attribute-Value) モデルを使用する

エンティティ、属性、値を別々のテーブルに格納するEAVモデルを使用します。このモデルでは、1つのエンティティに対して複数の属性を柔軟に定義することができます。

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE order_attributes (
  order_id INT NOT NULL,
  attribute_name VARCHAR(255) NOT NULL,
  attribute_value VARCHAR(255) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

この例では、ordersテーブルに注文IDのみを格納し、order_attributesテーブルに注文の詳細情報を属性名と値のペアとして格納します。

注意事項

上記で紹介した代替案はそれぞれ、メリットとデメリットがあります。どの方法を選択するかは、具体的な要件や状況に応じて判断する必要があります。

また、いずれの方法を選択する場合でも、データベース設計の原則を遵守し、データの整合性と構造の明確性を保つようにすることが重要です。

例1: 複合外部キーを使用したエラー

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id, product_id, order_date)
    REFERENCES orders_details(customer_id, product_id, order_date)
);

このコードを実行すると、以下のエラーが発生します。

ERROR 1062 (Duplicate key name): Duplicate key name 'customer_id,product_id,order_date'

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE order_details (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

ERROR 1062 (Duplicate key name): Duplicate key name 'order_id,product_id'

これらの例は、同じ外部キーを1つのテーブルで2回使用すると、エラーが発生することを示しています。

代替案

上記で紹介したように、同じ外部キーを2回使用したい場合は、以下の代替案を検討することができます。

中間テーブルを使用する

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_details (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  order_date DATE NOT NULL,
  PRIMARY KEY (order_id, customer_id, product_id, order_date)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE order_attributes (
  order_id INT NOT NULL,
  attribute_name VARCHAR(255) NOT NULL,
  attribute_value VARCHAR(255) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

注意事項

上記で紹介した代替案はあくまで一例であり、すべての状況に適用できるわけではありません。データベース設計を行う際には、要件を明確に定義し、適切な設計手法を用いることが重要です。

トリガーを使用して、関連するレコードが更新または削除されたときに、関連するレコードを自動的に更新または削除することができます。

CREATE TRIGGER update_order_details
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  UPDATE order_details
  SET quantity = NEW.quantity
  WHERE order_id = NEW.order_id AND product_id = NEW.product_id;
END;

CREATE TRIGGER delete_order_details
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  DELETE FROM order_details
  WHERE order_id = OLD.order_id;
END;

この例では、ordersテーブルが更新または削除されたときに、order_detailsテーブルを自動的に更新または削除するトリガーを作成しています。

ビューを使用して、複数のテーブルからのデータを1つの仮想テーブルとして表現することができます。ビューには、外部キー制約を含めることができないため、同じ外部キーを2回使用するという問題は発生しません。

CREATE VIEW order_details_with_product_name AS
SELECT o.order_id, o.customer_id, o.product_id, o.quantity, p.product_name
FROM orders o
JOIN products p ON o.product_id = p.product_id;

この例では、ordersテーブルとproductsテーブルのデータを結合した仮想テーブルを作成しています。

アプリケーションロジックを使用して、関連するレコードを明示的に更新または削除することができます。

def update_order_details(order_id, quantity):
  # Update order details in the database
  pass

def delete_order(order_id):
  # Delete order details and the order from the database
  pass