SQLite3でON DELETE CASCADEを使用する

SQLite3における「ON DELETE CASCADE」の仕組みと使用方法

SQLite3は、軽量で使い勝手の良いオープンソースのデータベース管理システム (DBMS) です。多くのプログラミング言語で利用可能であり、個人用プロジェクトからエンタープライズアプリケーションまで、幅広い用途に活用されています。

ON DELETE CASCADE は、リレーショナルデータベースにおいて参照整合性を維持するために使用される制約です。あるテーブルのレコードが削除された際、そのレコードを参照している他のテーブルの関連レコードも自動的に削除されるように設定します。

SQLite3で ON DELETE CASCADE を使用する方法は、2通りあります。

テーブル作成時に設定する

テーブルを作成する際に、FOREIGN KEY 制約と ON DELETE CASCADE オプションを組み合わせることで設定できます。

CREATE TABLE parent_table (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE child_table (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE
);

この例では、parent_table と child_table という2つのテーブルを作成しています。child_table の parent_id カラムは、parent_table の id カラムを参照する外部キー制約になっています。さらに、ON DELETE CASCADE オプションを指定することで、parent_table のレコードが削除された場合、それに関連する child_table のレコードも自動的に削除されるようになります。

既存のテーブルに ON DELETE CASCADE 制約を追加するには、ALTER TABLE ステートメントを使用します。

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE CASCADE;

この例では、child_table テーブルに fk_child_parent という名前の外部キー制約を追加しています。この制約は、parent_table の id カラムを参照し、ON DELETE CASCADE オプションを指定しています。

注意点

• ON DELETE CASCADE を使用する前に、PRAGMA foreign_keys = ON; ステートメントを実行して、外部キー制約を有効にする必要があります。
• ON DELETE CASCADE は、参照整合性を維持するためにのみ使用されます。データの整合性を保証するものではありません。
• ON DELETE CASCADE を使用すると、予期しないレコード削除が発生する可能性があります。使用する前に、十分な注意が必要です。

• 上記以外にも、SQLite3で ON DELETE CASCADE を使用する方法はいくつかあります。詳細は、SQLite3 ドキュメントを参照してください。
• データベースの操作を行う前に、必ずバックアップを取ることを忘れないでください。

補足

• この説明は、SQLite3 バージョン 3.36.0 を対象としています。
• 他のバージョンの SQLite3 では、動作が異なる場合があります。

SQLite3におけるON DELETE CASCADEのサンプルコード

このサンプルコードでは、ON DELETE CASCADE を使用して、SQLite3データベースで参照整合性を維持する方法を示します。

シナリオ

以下の2つのテーブルを作成します。

• customers テーブル: 顧客情報 (名前、住所、電話番号) を格納します。
• orders テーブル: 注文情報 (顧客ID、注文日、注文内容) を格納します。

orders テーブルの customer_id カラムは、customers テーブルの id カラムを参照する外部キー制約になります。さらに、ON DELETE CASCADE オプションを指定することで、customers テーブルのレコードが削除された場合、それに関連する orders テーブルのレコードも自動的に削除されるようになります。

コード

-- customersテーブルを作成
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  address TEXT,
  phone_number TEXT
);

-- ordersテーブルを作成
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date DATE,
  order_details TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- 顧客データを挿入
INSERT INTO customers (name, address, phone_number) VALUES
  ('山田太郎', '東京都千代田区', '03-1234-5678'),
  ('佐藤花子', '大阪府大阪市', '06-9876-5432');

-- 注文データを挿入
INSERT INTO orders (customer_id, order_date, order_details) VALUES
  (1, '2024-04-01', 'パソコン'),
  (1, '2024-04-08', '書籍'),
  (2, '2024-04-15', '衣類');

-- 顧客ID 1番の顧客を削除
DELETE FROM customers WHERE id = 1;

-- 削除後のデータベースを確認
SELECT * FROM customers;
SELECT * FROM orders;

実行結果

-- customersテーブル

id | name       | address     | phone_number
---|------------|------------|--------------
2  | 佐藤花子   | 大阪府大阪市 | 06-9876-5432

-- ordersテーブル

id | customer_id | order_date | order_details
---|------------|------------|--------------
2  | 2          | 2024-04-15 | 衣類

上記の結果のように、顧客ID 1番の顧客が削除されると、それに関連する orders テーブルのレコードも自動的に削除されます。

• このサンプルコードは、あくまでも理解を深めるためのものです。実際のアプリケーションでは、より複雑なクエリや操作を行う可能性があります。

SQLite3におけるON DELETE CASCADEの代替方法

ON DELETE CASCADE は、SQLite3で参照整合性を維持するための便利な制約ですが、状況によっては他の方法の方が適切な場合があります。

代替方法

以下に、ON DELETE CASCADE の代替方法をいくつか紹介します。

トリガーを使用する

トリガーは、データベース操作が発生した際に自動的に実行されるコードです。ON DELETE トリガーを使用して、レコードが削除されたときに関連レコードを処理することができます。

CREATE TRIGGER delete_order_trigger
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  DELETE FROM orders WHERE customer_id = NEW.id;
END;

この例では、customers テーブルからレコードが削除される前に、orders テーブルから関連レコードを削除する delete_order_trigger というトリガーを作成しています。

手動で削除する

アプリケーションコードでレコードを削除する際に、関連レコードも手動で削除することができます。

import sqlite3

def delete_customer(customer_id):
  # customersテーブルから顧客を削除
  connection = sqlite3.connect('database.db')
  cursor = connection.cursor()
  cursor.execute('DELETE FROM customers WHERE id = ?', (customer_id,))
  connection.commit()
  connection.close()

  # ordersテーブルから関連注文を削除
  connection = sqlite3.connect('database.db')
  cursor = connection.cursor()
  cursor.execute('DELETE FROM orders WHERE customer_id = ?', (customer_id,))
  connection.commit()
  connection.close()

# 顧客ID 1番の顧客を削除
delete_customer(1)

この例では、delete_customer という関数を作成し、顧客と関連注文を削除しています。

NULL値を使用する

外部キー制約を ON DELETE SET NULL オプションで設定することで、レコードが削除されたときに関連レコードの外部キーカラムに NULL 値を設定することができます。

CREATE TABLE child_table (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON DELETE SET NULL
);

この例では、child_table テーブルの parent_id カラムに NULL 値を設定するように設定しています。

• シンプルさ を重視する場合は、ON DELETE CASCADE を使用する方が簡単です。
• 柔軟性 を重視する場合は、トリガーや手動削除を使用することで、より複雑な処理を行うことができます。
• パフォーマンス を重視する場合は、ON DELETE SET NULL を使用する方が効率的になる場合があります。