トリガー、チェック制約、カスケード更新…SQLiteで外部キー制約に代わる方法

2024-05-21

SQLiteにおける外部キー制約：詳細ガイド

概要

本ガイドでは、SQLiteにおける外部キー制約の仕組み、作成方法、様々なオプション、そして実用的な例を通して、その概念と利点を深く理解していきます。

外部キー制約とは？

外部キー制約は、子テーブルの列と親テーブルの主キー列との間で論理的な関係を定義する制約です。この関係により、子テーブルの列に格納される値は、常に親テーブルの主キーに存在する値と一致するようになります。

例えば、顧客テーブルと注文テーブルがあると仮定しましょう。顧客テーブルには、顧客ID（主キー）と顧客名などの列が含まれます。一方、注文テーブルには、注文ID（主キー）、顧客ID（外部キー）、商品名などの列が含まれます。

この場合、注文テーブルの顧客ID列に設定される値は、常に顧客テーブルの顧客ID列に存在する値と一致する必要があります。つまり、注文が存在するには、必ず対応する顧客が存在するということです。

外部キー制約を設定することで、データベースの整合性を保ち、データの信頼性を高めることができます。具体的には、以下の利点が挙げられます。

データの整合性: 子テーブルのデータが常に親テーブルのデータと一致するように保証します。
データの参照整合性: 無効な参照を防ぎ、データベースの破損を回避します。
データの更新整合性: 親テーブルのデータが更新された場合、子テーブルの関連データも自動的に更新されます。

外部キー制約の構文

SQLiteにおける外部キー制約は、CREATE TABLEステートメント内で以下の構文を使用して定義されます。

CREATE TABLE child_table (
  ...
  foreign_key_column REFERENCES parent_table(parent_key_column)
  [ON DELETE action]
  [ON UPDATE action]
);

例:

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

上記の例では、ordersテーブルのcustomer_id列が、customersテーブルのcustomer_id列を参照する外部キー制約として定義されています。

構文の詳細:

foreign_key_column: 子テーブルの列名
parent_table: 親テーブル名
ON DELETE action: 親テーブルのデータ削除時の処理を定義 (オプション)

外部キー制約の種類

SQLiteでは、以下の種類の外部キー制約を定義することができます。

参照制約: 子テーブルの列が、親テーブルの主キー列を参照する基本的な制約です。
遅延制約: 制約のチェックをコミット時まで延期する制約です。

外部キー制約オプション

外部キー制約には、以下のオプションを設定することができます。

ON DELETE action: 親テーブルのデータが削除された際に、子テーブルの関連データに対して実行するアクションを指定します。以下のアクションが可能です。
- RESTRICT: デフォルト設定。削除しようとするデータが存在する場合はエラーが発生します。
- CASCADE: 関連する子データを自動的に削除します。
- SET NULL: 関連する子データの列をNULL値に設定します。

外部キー制約の例

例1：顧客と注文の関係

-- 顧客テーブル (customers)
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);

-- 注文テーブル (orders)
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
);

customersテーブルは、顧客ID、氏名、姓を格納します。
ordersテーブルは、注文ID、顧客ID、注文日、顧客テーブルへの外部キーを格納します。

ON DELETE CASCADEオプションにより、customersテーブルの顧客が削除されると、関連するordersテーブルの注文も自動的に削除されます。

例2：商品とカテゴリの関係

-- 商品テーブル (products)
CREATE TABLE products (
  product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name TEXT NOT NULL,
  product_price REAL NOT NULL,
  category_id INTEGER NOT NULL,
  FOREIGN KEY (category_id) REFERENCES categories(category_id)
  ON UPDATE RESTRICT
);

-- カテゴリテーブル (categories)
CREATE TABLE categories (
  category_id INTEGER PRIMARY KEY AUTOINCREMENT,
  category_name TEXT NOT NULL
);

この例では、productsテーブルとcategoriesテーブル間で外部キー制約を定義しています。

productsテーブルは、商品ID、商品名、価格、カテゴリID、カテゴリテーブルへの外部キーを格納します。
categoriesテーブルは、カテゴリIDとカテゴリ名を格納します。

ON UPDATE RESTRICTオプションにより、categoriesテーブルのカテゴリが更新された場合、関連するproductsテーブルの商品は自動的に更新されません。更新処理を行うには、アプリケーション側でロジックを実装する必要があります。

SQLiteの外部キー制約は、データベースの整合性を保ち、データの信頼性を高めるために不可欠なツールです。

このガイドで説明した概念と構文を理解することで、効率的で堅牢なデータベース設計が可能になります。

SQLiteにおける外部キー制約：代替方法

状況によっては、これらの方法がより適切な場合もあります。以下に、代表的な代替方法とその概要を紹介します。

トリガーは、データベース操作に応じて自動的に実行される一連のSQLステートメントです。外部キー制約と同等の機能を提供するために使用することができます。

CREATE TRIGGER orders_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  IF (SELECT 1 FROM customers WHERE customer_id = OLD.customer_id) IS NULL THEN
    RAISE ROLLBACK '無効な顧客IDが指定されました';
  END IF;
END;

この例では、ordersテーブルからレコードを削除しようとする前にトリガーが実行されます。トリガーは、削除対象のレコードのcustomer_idがcustomersテーブルに存在するかどうかを確認します。存在しない場合は、エラーメッセージを表示して削除を中止します。

トリガーの利点は、外部キー制約よりも柔軟な制御が可能であることです。ただし、複雑なロジックを実装する場合には、トリガーのコードが煩雑になり、メンテナンスが困難になる可能性があります。

チェック制約は、列の値が特定の条件を満たしているかどうかを検証する制約です。外部キー制約で使用されている参照整合性を独自に実装するために使用することができます。

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  CONSTRAINT fk_orders_customers CHECK (customer_id IN (SELECT customer_id FROM customers))
);

この例では、ordersテーブルのcustomer_id列にチェック制約が設定されています。この制約は、customer_idの値がcustomersテーブルに存在する顧客IDであることを確認します。

チェック制約の利点は、シンプルなロジックを簡単に実装できることです。ただし、複雑な参照整合性を表現するには、複数のチェック制約を組み合わせる必要があり、煩雑になる可能性があります。

カスケード更新は、親テーブルのデータが更新された際に、関連する子テーブルのデータも自動的に更新する機能です。外部キー制約のON UPDATE CASCADEオプションと同様の動作ですが、トリガーやチェック制約を使用するよりも簡潔に記述できます。

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON UPDATE CASCADE
);