Androidアプリ開発におけるSQLiteと外部キー

2024-04-10

SQLiteで外部キー付きテーブルを作成する構文

構文

CREATE TABLE 子テーブル名 (
  子テーブルの列名 型,
  ...
  FOREIGN KEY (子テーブルの列名) REFERENCES 親テーブル名 (親テーブルの列名)
);

例:

CREATE TABLE 注文 (
  注文ID INTEGER PRIMARY KEY AUTOINCREMENT,
  顧客ID INTEGER,
  商品ID INTEGER,
  FOREIGN KEY (顧客ID) REFERENCES 顧客 (顧客ID),
  FOREIGN KEY (商品ID) REFERENCES 商品 (商品ID)
);

説明:

CREATE TABLE はテーブルを作成するコマンドです。
子テーブル名 は作成するテーブルの名前です。
( ) 内は、テーブルの列とそのデータ型を指定します。
FOREIGN KEY は外部キー制約を定義します。
(子テーブルの列名) は、外部キーとなる列の名前です。
REFERENCES は参照先のテーブルを指定します。

オプション

ON DELETE CASCADE: 親テーブルのレコードが削除された場合、子テーブルの関連するレコードも自動的に削除されます。

CREATE TABLE 注文 (
  注文ID INTEGER PRIMARY KEY AUTOINCREMENT,
  顧客ID INTEGER,
  商品ID INTEGER,
  FOREIGN KEY (顧客ID) REFERENCES 顧客 (顧客ID) ON DELETE CASCADE,
  FOREIGN KEY (商品ID) REFERENCES 商品 (商品ID) ON DELETE RESTRICT
);

SQLiteで外部キー付きテーブルを作成するには、CREATE TABLE コマンドと FOREIGN KEY 制約を使用します。外部キーは、複数のテーブル間でデータの関連性を保つために重要です。

補足

SQLiteは、ANSI SQL とは一部異なる構文を使用している場合があります。
詳細については、SQLite の公式ドキュメントを参照してください。

-- テーブル作成
CREATE TABLE 顧客 (
  顧客ID INTEGER PRIMARY KEY AUTOINCREMENT,
  名前 TEXT,
  年齢 INTEGER
);

CREATE TABLE 注文 (
  注文ID INTEGER PRIMARY KEY AUTOINCREMENT,
  顧客ID INTEGER,
  商品ID INTEGER,
  FOREIGN KEY (顧客ID) REFERENCES 顧客 (顧客ID),
  FOREIGN KEY (商品ID) REFERENCES 商品 (商品ID)
);

CREATE TABLE 商品 (
  商品ID INTEGER PRIMARY KEY AUTOINCREMENT,
  名前 TEXT,
  価格 INTEGER
);

データ挿入

-- データ挿入
INSERT INTO 顧客 (名前, 年齢) VALUES ("山田太郎", 30);
INSERT INTO 顧客 (名前, 年齢) VALUES ("佐藤花子", 25);

INSERT INTO 商品 (名前, 価格) VALUES ("ノートパソコン", 100000);
INSERT INTO 商品 (名前, 価格) VALUES ("スマートフォン", 80000);

INSERT INTO 注文 (顧客ID, 商品ID) VALUES (1, 1);
INSERT INTO 注文 (顧客ID, 商品ID) VALUES (2, 2);

データ参照

-- データ参照
SELECT * FROM 注文;

-- 結果
-- 注文ID | 顧客ID | 商品ID
-- ------- | -------- | --------
-- 1       | 1       | 1
-- 2       | 2       | 2

SELECT 注文.注文ID, 顧客.名前, 商品.名前
FROM 注文
INNER JOIN 顧客 ON 注文.顧客ID = 顧客.顧客ID
INNER JOIN 商品 ON 注文.商品ID = 商品.商品ID;

-- 結果
-- 注文ID | 名前      | 名前
-- ------- | -------- | --------
-- 1       | 山田太郎 | ノートパソコン
-- 2       | 佐藤花子 | スマートフォン

外部キー制約の確認

-- 外部キー制約の確認
DELETE FROM 顧客 WHERE 顧客ID = 1;

-- エラー:
-- FOREIGN KEY constraint failed

外部キー制約のオプション

-- 外部キー制約のオプション

CREATE TABLE 注文 (
  注文ID INTEGER PRIMARY KEY AUTOINCREMENT,
  顧客ID INTEGER,
  商品ID INTEGER,
  FOREIGN KEY (顧客ID) REFERENCES 顧客 (顧客ID) ON DELETE CASCADE,
  FOREIGN KEY (商品ID) REFERENCES 商品 (商品ID) ON DELETE RESTRICT
);

-- 顧客ID を削除すると、関連する注文も自動的に削除される
DELETE FROM 顧客 WHERE 顧客ID = 1;

-- 商品ID を削除しようとすると、エラーが発生する
DELETE FROM 商品 WHERE 商品ID = 1;

-- エラー:
-- FOREIGN KEY constraint failed

補足

このサンプルコードは、SQLite 3 で動作します。
他のデータベースエンジンを使用する場合は、構文が異なる場合があります。

SQLiteで外部キー付きテーブルを作成する他の方法

REFERENCES 句を使用しない方法

CREATE TABLE 注文 (
  注文ID INTEGER PRIMARY KEY AUTOINCREMENT,
  顧客ID INTEGER,
  商品ID INTEGER,
  CHECK (顧客ID IN (SELECT 顧客ID FROM 顧客)),
  CHECK (商品ID IN (SELECT 商品ID FROM 商品))
);

CHECK 制約を使用して、外部キー列の値が参照先のテーブルの値に存在することを確認します。

外部キートリガーを使用して、外部キー制約違反を処理することもできます。

CREATE TRIGGER 注文_顧客ID_FK
BEFORE INSERT ON 注文
FOR EACH ROW
BEGIN
  SELECT 1
  FROM 顧客
  WHERE 顧客ID = NEW.顧客ID;
END;

CREATE TRIGGER 注文_商品ID_FK
BEFORE INSERT ON 注文
FOR EACH ROW
BEGIN
  SELECT 1
  FROM 商品
  WHERE 商品ID = NEW.商品ID;
END;