MariaDB講座 / 主キーと外部キーで表の関係を設計する

表の関係は、一行が表す事実を定め、主キーと外部キーで結びます。一顧客に複数注文があり、注文と商品の多対多関係は数量と購入時単価を持つ明細表で表せます。

一行の意味と関係の向きを定める

顧客名と商品リストを注文の一つの文字列へ入れると、数量の計算や名前の変更が分解処理と重複に依存します。単に分割するのでなく、独立して更新する事実と守る規則で分けます。

明細のunit_priceは購入時単価です。商品の現在価格が変わっても過去の金額を再現できます。同じ注文で同一商品の異なるオプションを複数明細にするなら、この単純な複合キーでは足りず明細IDやオプションキーを検討します。

外部キー付きの完全な例を作る

クライアントの接続手順でcourse_userとしてshopcourseへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;を確認します。以下には必要な四表の作成とデータがすべてあり、以前の記事の表を必要としません。UTF-8の.sqlファイルで実行し、同名の表があれば定義と内容を調べてから判断します。

USE shopcourse;
CREATE TABLE c25_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
  REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
  REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
  REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

顧客名・都市・商品名は原文の韓国語サンプルです。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。文字列を維持して実行し、説明とは安定したIDで照合できます。

参照される顧客と商品を先に作り、注文と明細を後に作成・追加します。複合主キーの意味は行の識別子と合わせて確認します。

一対多と多対多を確認する

定義と実際の関連を読みます。

SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;

顧客4、注文4、明細6行です。顧客1は注文101と102を持ち、注文101には商品1と2があります。商品1は注文101と103の両方に登場します。注文と商品の関連自体が、数量・購入時価格を持つ一行です。

名前が同じ顧客や名前変更を考えると、関連には安定したIDを使います。名前が変わるたびに注文の関連を作り直す構造を避けます。INNER JOINはこのキーを読み取りに使います。

存在しない親と参照中の削除を試す

通常例とは分け、一文ずつ実行します。

INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');

DELETE FROM c25_customers WHERE customer_id=1;

顧客999への注文追加と、注文が残る顧客1の削除はいずれも外部キーで拒否されます。顧客1と注文4件が残るか読み戻します。foreign_key_checksを無効にして通さないでください。別設計ではNULLの親参照を許すこともありますが、この注文の顧客列はNOT NULLです。

削除方針と整合性の範囲

CASCADEは親削除を子へ伝えるため、便利という理由だけで選びません。退会時の保存・匿名化・削除や、会計・返品の履歴を考え、この例はRESTRICTで注文の自動削除を避けています。

キーの型と符号、索引、エンジンを確認します。定義と不正入力の拒否を合わせると、参照らしい文字がスクリプトにあるだけより確実です。ただし外部キーは在庫充足など全業務規則を検査しません。注文なしの顧客や未販売商品を許すかは正規化と注文設計でも整理します。

再実習はDB名、実際の定義と保存行を確認し、必要なデータを保存してから行います。この記事の削除してよい四表だけを、参照する子から親の順に削除します。DROP TABLEは定義とデータを消し、通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;

関連する公式リファレンス

• foreign keys（公式ドキュメント）