SQLite: 外部キー制約でデータベース設計をレベルアップ！無効化の危険性と有効化の全方法

2024-06-30

SQLiteにおける外部キー制約とデータ整合性

SQLiteは軽量で使いやすいデータベースとして人気がありますが、デフォルトでは外部キー制約によるデータ整合性が保たれないという点に注意が必要です。この制約は、関連するテーブル間でデータの整合性を保証する重要な役割を果たします。

外部キー制約は、あるテーブル（子テーブル）の列を、別のテーブル（親テーブル）の主キー列を参照するように設定するものです。これにより、子テーブルのデータが常に親テーブルの既存データを参照することを保証します。

SQLiteでは、外部キー制約はデフォルトで無効になっています。つまり、子テーブルに存在しない親テーブルのIDが設定されても、エラーが発生せず、データの整合性が失われる可能性があります。

データ整合性の問題

外部キー制約が無効の場合、以下のようなデータ整合性の問題が発生する可能性があります。

子テーブルに存在しない親テーブルのIDが設定される
親テーブルのレコードが削除された後も、子テーブルに参照が残っている

外部キー制約の有効化

SQLiteで外部キー制約を有効にするには、以下のコマンドを実行する必要があります。

PRAGMA foreign_keys = ON;

このコマンドを実行すると、すべてのデータベースに対して外部キー制約が有効になります。

SQLiteでは、以下の種類の外部キー制約がサポートされています。

単純外部キー制約: 子テーブルの1つの列が、親テーブルの1つの列を参照します。
遅延外部キー制約: 外部キー制約のチェックが、データ挿入後ではなく、コミット時まで延期されます。

ON DELETEおよびON UPDATEオプション

外部キー制約には、親テーブルのレコードが削除または更新された場合に、子テーブルのレコードに対してどのような処理を行うかを指定するオプションがあります。

ON DELETE CASCADE: 親テーブルのレコードが削除されると、子テーブルを参照しているレコードも自動的に削除されます。

SQLiteは、外部キー制約によるデータ整合性をデフォルトで保たないため、注意が必要です。外部キー制約を有効にすることで、データ整合性を保証することができます。また、ON DELETEおよびON UPDATEオプションを使用して、親テーブルのレコードの変更が子テーブルにどのように反映されるかを制御することができます。

SQLiteにおける外部キー制約のサンプルコード

customers テーブル: 顧客情報を格納します。
- customer_id (主キー)
- name
- city
orders テーブル: 注文情報を格納します。
- product_id
- order_date

まず、customers テーブルを作成します。

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

次に、orders テーブルを作成し、customer_id 列に外部キー制約を設定します。

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

この外部キー制約により、orders テーブルの customer_id 列に設定される値は、常に customers テーブルの customer_id 列に存在する値であることが保証されます。

以下に、いくつかのデータ挿入例を示します。

INSERT INTO customers (name, city) VALUES ('John Doe', 'New York');
INSERT INTO customers (name, city) VALUES ('Jane Doe', 'Los Angeles');

INSERT INTO orders (customer_id, product_id, order_date) VALUES (1, 201, '2024-06-29');
INSERT INTO orders (customer_id, product_id, order_date) VALUES (2, 302, '2024-06-29');

customer_id 2 が存在しないため、2番目の INSERT ステートメントはエラーとなります。

Error: FOREIGN KEY constraint failed

PRAGMA foreign_keys = OFF;

このコマンドを実行すると、orders テーブルに存在しない customer_id を設定してもエラーが発生しなくなります。

INSERT INTO orders (customer_id, product_id, order_date) VALUES (2, 302, '2024-06-29');

ただし、この状態ではデータ整合性が保証されないことに注意する必要があります。

この例は、SQLiteにおける外部キー制約の基本的な使用方法を示しています。外部キー制約は、データ整合性を保証する重要なツールです。データベース設計において、適切に使用するようにしましょう。

SQLite で外部キー制約を有効にするその他の方法

PRAGMA コマンドを使用する

これは、最も一般的な方法です。以下のコマンドを実行するだけです。

PRAGMA foreign_keys = ON;

データベース接続時にオプションを指定する

データベース接続時に foreign_keys オプションを True に設定することで、外部キー制約を有効にすることができます。

Python の場合

import sqlite3

connection = sqlite3.connect('mydatabase.db', foreign_keys=True)

Java の場合

import java.sql.*;

Connection connection = DriverManager.getConnection("jdbc:sqlite:mydatabase.db", "?foreign_keys=ON");

C# の場合

using System.Data.SQLite;

using (var connection = new SQLiteConnection("Data Source=mydatabase.db;Foreign Keys=True"))
{
  connection.Open();
  // ...
}

CREATE TABLE ステートメント内に PRAGMA foreign_keys を含める

CREATE TABLE ステートメント内に PRAGMA foreign_keys を含めることで、そのテーブルに対してのみ外部キー制約を有効にすることができます。

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  PRAGMA foreign_keys = ON,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

データベースファイルを開く前に PRAGMA foreign_keys を実行する

PRAGMA foreign_keys = ON;
.open mydatabase.db

すべてのデータベースに対して外部キー制約を有効にする場合は、PRAGMA コマンドを使用する 方法が最も簡単です。
特定のデータベース接続に対してのみ外部キー制約を有効にする場合は、データベース接続時にオプションを指定する 方法が適しています。
特定のテーブルに対してのみ外部キー制約を有効にする場合は、CREATE TABLE ステートメント内に PRAGMA foreign_keys を含める 方法が適しています。
スクリプトを実行する前に外部キー制約を有効にする場合は、データベースファイルを開く前に PRAGMA foreign_keys を実行する 方法が適しています。