テーブル間の関係
顧客と注文の一対多、顧客とプロフィールの一対一、注文と商品の多対多を順に作ります。外部キーで参照を検査し、存在しない親の追加や参照中の削除が拒否されることを確認します。最後に明細の行を使って、どの顧客がどの商品を注文したかを取得します。
説明
複数の表へ情報を分けると、同じ顧客名や商品名を注文ごとに繰り返さずに保存できます。その代わり、どの行を参照するかをIDで表し、存在しない参照を作らないようにする必要があります。
顧客と注文の一対多、顧客とプロフィールの一対一、注文と商品の多対多を順に作ります。外部キーで参照を検査し、存在しない親の追加や参照中の削除が拒否されることを確認します。最後に明細の行を使って、どの顧客がどの商品を注文したかを取得します。
1外部キー検査を有効にする
SQLiteの外部キー検査を明示的に有効にし、現在値を確認します。
PRAGMA foreign_keys=ON外部キー検査を有効にする;
PRAGMA foreign_keys;現在の設定を取得1は有効を表します。SQLiteでは接続ごとに設定を確認します。外部キーをDDLへ書いたことと、その接続で検査が有効であることを同じものとして扱わないようにします。
2顧客表を作る
顧客IDと名前を持つ二行を用意します。
CREATE TABLE customers(id INTEGER PRIMARY KEY顧客を識別するID,name TEXT NOT NULL必須の表示名) STRICT;
INSERT INTO customers二人の顧客を追加 VALUES(1,'あおい'),(2,'はる');
SELECT * FROM customers ORDER BY id;顧客IDで行を識別します。名前は同名の人があり得るため、この表では一意性を要求していません。IDと表示名は役割が異なります。
3一人に複数の注文を対応させる
注文表に顧客への外部キーを付け、三件を追加します。
CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customers(id)顧客を必須の参照先にする,ordered_on TEXT NOT NULL) STRICT;
INSERT INTO orders VALUES(101,1,'2026-11-01'),(102,1,'2026-11-02')同じ顧客に二つの注文,(103,2,'2026-11-02');
SELECT * FROM orders ORDER BY id;顧客1には注文101と102、顧客2には103が対応します。外部キーは参照先の存在を検査しますが、customer_idの重複は禁止しないため一対多になります。日付はこの例ではISO形式のTEXTで、型指定だけで日付の全規則を検査しているわけではありません。
4存在しない顧客への注文
顧客99を参照する注文を追加します。
INSERT INTO orders VALUES(104,99新しい注文から存在しない顧客を参照,'2026-11-03');FOREIGN KEY制約で拒否されます。単に整数99として保存できることと、そのIDの顧客が存在することは別の条件です。
5参照されている顧客を削除する
注文を持つ顧客1を削除します。
DELETE FROM customers WHERE id=1参照先の削除を試す;
SELECT id,name拒否後の顧客を確認 FROM customers ORDER BY id;削除は拒否され、二人の顧客が残ります。ここでは削除時の連動処理を指定していないため、注文の参照先を失う削除は通りません。削除を連動させるか、保持するかはデータの寿命に合わせて決めます。
6一人に一つまでのプロフィール
customer_idを主キー兼外部キーにします。
CREATE TABLE profiles(customer_id INTEGER PRIMARY KEY REFERENCES customers(id)参照と一意性を兼ねる,phone TEXT) STRICT;
INSERT INTO profiles VALUES(1,'000-1111')顧客1のプロフィール;
SELECT * FROM profiles;外部キーで存在する顧客だけを許し、主キーの一意性で一人につき一行までに制限します。ただし顧客2のプロフィールはまだなく、すべての顧客に必ず一行存在することまでは保証していません。厳密には顧客一行に対してプロフィールは0または1行です。
7同じ顧客のプロフィールを重ねる
顧客1へ二件目を追加します。
INSERT INTO profiles VALUES(1,'000-2222')同じ顧客に二行目を追加;主キーの重複で拒否されます。外部キーだけでは一対一にならず、参照する側の列にも一意性が必要です。
8商品と注文の明細を作る
商品表と、注文ID・商品IDを組にする明細表を作ります。
CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO products VALUES(10,'ノート'),(20,'ペン');
CREATE TABLE order_items関係を明細表として保存(order_id INTEGER REFERENCES orders(id),product_id INTEGER REFERENCES products(id),qty INTEGER NOT NULL CHECK(qty>0)組ごとの正の数量,PRIMARY KEY(order_id,product_id)注文と商品の組を一意にする) STRICT;
INSERT INTO order_items VALUES(101,10,2),(101,20,1),(102,10,3),(103,20,2);
SELECT * FROM order_items ORDER BY order_id,product_id;注文101には二商品があり、商品10は注文101と102へ入っています。注文と商品の多対多を、中間の明細表による二つの一対多へ分けました。qtyは注文と商品の組に属する値です。
9関係をたどって一覧を取得する
顧客・注文・明細・商品をIDで結びます。
SELECT c.name AS customer,o.id AS order_id,p.name AS product,i.qty FROM customers AS c JOIN orders AS o ON o.customer_id=c.id顧客から注文へ JOIN order_items AS i ON i.order_id=o.id注文から明細へ JOIN products AS p ON p.id=i.product_id明細から商品名へ ORDER BY o.id,p.id;一行は注文内の商品明細です。あおいは三明細、はるは一明細になり、顧客の行数と結果の行数は一致しません。どの単位の結果が必要かを決め、結合するキーを対応させます。
10存在しない商品は明細へ入らない
商品99を含む明細を追加します。
INSERT INTO order_items VALUES(102,99,1存在しない商品を指定);
SELECT COUNT(*)拒否後の明細数 AS item_rows FROM order_items;商品への外部キー違反となり、明細は4行のままです。中間表にも両側への参照制約が必要で、複合主キーだけでは参照先の存在は保証されません。
11参照制約の定義と整合性
明細の外部キー定義と、違反行の有無を取得します。
PRAGMA foreign_key_list(order_items)定義された二つの参照;
PRAGMA foreign_key_check保存データの参照違反を検査;order_id→orders.idとproduct_id→products.idの二つが定義されています。foreign_key_checkは違反行があれば返しますが、この状態では追加の行はありません。設定・定義・保存された行の三つを合わせて、関係が保たれていることを確認します。
まとめ
外部キーは参照先の存在を検査し、一意性は関係に許す行数を決めます。
一対一でも、親の全行に子が存在することまで保証するとは限りません。
多対多の関係は中間表へ分け、関係に属する値と両側への参照を保存します。
操作履歴と解説を振り返れます。
SQLite 3.54.0
PRAGMA foreign_keys=ON;
PRAGMA foreign_keys;╭──────────────╮ │ foreign_keys │ ╞══════════════╡ │ 1 │ ╰──────────────╯
CREATE TABLE customers(id INTEGER PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO customers VALUES(1,'あおい'),(2,'はる');
SELECT * FROM customers ORDER BY id;╭────┬────────╮ │ id │ name │ ╞════╪════════╡ │ 1 │ あおい │ │ 2 │ はる │ ╰────┴────────╯
CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER NOT NULL REFERENCES customers(id),ordered_on TEXT NOT NULL) STRICT;
INSERT INTO orders VALUES(101,1,'2026-11-01'),(102,1,'2026-11-02'),(103,2,'2026-11-02');
SELECT * FROM orders ORDER BY id;╭─────┬─────────────┬────────────╮ │ id │ customer_id │ ordered_on │ ╞═════╪═════════════╪════════════╡ │ 101 │ 1 │ 2026-11-01 │ │ 102 │ 1 │ 2026-11-02 │ │ 103 │ 2 │ 2026-11-02 │ ╰─────┴─────────────┴────────────╯
INSERT INTO orders VALUES(104,99,'2026-11-03');Error: FOREIGN KEY constraint failed
DELETE FROM customers WHERE id=1;
SELECT id,name FROM customers ORDER BY id;Error: FOREIGN KEY constraint failed ╭────┬────────╮ │ id │ name │ ╞════╪════════╡ │ 1 │ あおい │ │ 2 │ はる │ ╰────┴────────╯
CREATE TABLE profiles(customer_id INTEGER PRIMARY KEY REFERENCES customers(id),phone TEXT) STRICT;
INSERT INTO profiles VALUES(1,'000-1111');
SELECT * FROM profiles;╭─────────────┬──────────╮ │ customer_id │ phone │ ╞═════════════╪══════════╡ │ 1 │ 000-1111 │ ╰─────────────┴──────────╯
INSERT INTO profiles VALUES(1,'000-2222');Error: UNIQUE constraint failed: profiles.customer_id
CREATE TABLE products(id INTEGER PRIMARY KEY,name TEXT NOT NULL) STRICT;
INSERT INTO products VALUES(10,'ノート'),(20,'ペン');
CREATE TABLE order_items(order_id INTEGER REFERENCES orders(id),product_id INTEGER REFERENCES products(id),qty INTEGER NOT NULL CHECK(qty>0),PRIMARY KEY(order_id,product_id)) STRICT;
INSERT INTO order_items VALUES(101,10,2),(101,20,1),(102,10,3),(103,20,2);
SELECT * FROM order_items ORDER BY order_id,product_id;╭──────────┬────────────┬─────╮ │ order_id │ product_id │ qty │ ╞══════════╪════════════╪═════╡ │ 101 │ 10 │ 2 │ │ 101 │ 20 │ 1 │ │ 102 │ 10 │ 3 │ │ 103 │ 20 │ 2 │ ╰──────────┴────────────┴─────╯
SELECT c.name AS customer,o.id AS order_id,p.name AS product,i.qty FROM customers AS c JOIN orders AS o ON o.customer_id=c.id JOIN order_items AS i ON i.order_id=o.id JOIN products AS p ON p.id=i.product_id ORDER BY o.id,p.id;╭──────────┬──────────┬─────────┬─────╮ │ customer │ order_id │ product │ qty │ ╞══════════╪══════════╪═════════╪═════╡ │ あおい │ 101 │ ノート │ 2 │ │ あおい │ 101 │ ペン │ 1 │ │ あおい │ 102 │ ノート │ 3 │ │ はる │ 103 │ ペン │ 2 │ ╰──────────┴──────────┴─────────┴─────╯
INSERT INTO order_items VALUES(102,99,1);
SELECT COUNT(*) AS item_rows FROM order_items;Error: FOREIGN KEY constraint failed ╭───────────╮ │ item_rows │ ╞═══════════╡ │ 4 │ ╰───────────╯
PRAGMA foreign_key_list(order_items);
PRAGMA foreign_key_check;╭────┬─────┬──────────┬────────────┬────┬───────────┬───────────┬───────╮ │ id │ seq │ table │ from │ to │ on_update │ on_delete │ match │ ╞════╪═════╪══════════╪════════════╪════╪═══════════╪═══════════╪═══════╡ │ 0 │ 0 │ products │ product_id │ id │ NO ACTION │ NO ACTION │ NONE │ │ 1 │ 0 │ orders │ order_id │ id │ NO ACTION │ NO ACTION │ NONE │ ╰────┴─────┴──────────┴────────────┴────┴───────────┴───────────┴───────╯
演習完了