FOREIGN KEY制約の設定
子を先に作ると、その場で落ちる
外部キーを書いた CREATE TABLE をファイルの上から順に流したら、いきなりエラーで止まった。よくある詰まり方で、原因は順番です。REFERENCES customers(id) と書いた時点で customers が存在していないと、DB は参照先を確かめられません。親を先に作り、子を後に作る。この順序は動かせません。
同じ理由で、片付けるときは逆になります。子のテーブルを先に消さないと、親のテーブルは消せません。
書き方は 3 通り、名前は自分で付ける
SQL クエリ
-- 列の定義の中に書く
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id)
);
-- 定義の末尾にまとめて書く。名前を付けられる
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- すでにあるテーブルに後から足す
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);1 つ目が最短ですが、制約の名前は DB が勝手に決めます。名前が要るのは、外したり付け替えたりするときです。
SQL クエリ
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;自動で付いた名前は orders_customer_id_fkey のような形で、DBMS ごとに規則が違います。運用中に「この制約を一時的に外したい」となってから調べるのは面倒なので、最初から fk_子テーブル_親テーブル の形で自分で名前を書いておくのが定石です。
後から足すときは、先にデータを直す
ALTER TABLE での後付けは、既存の全行が条件を満たしているかを DB が確かめてから通ります。親に存在しない値を持つ行が 1 つでもあれば、ALTER そのものが失敗します。
つまり手順は 3 段階になります。まず孤児を洗い出し、次に消すか正しい親に付け替え、それから制約を足します。この順番を飛ばすと、エラーメッセージだけを見て「このテーブルには制約を張れない」と誤解することになります。制約が拒んでいるのは、すでに壊れている今のデータです。
制約を外す構文は DBMS で分かれます。
DROP CONSTRAINTが通らない環境ではALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;のように書きます。手元の DB で一度試してから、本番の手順書に写してください。
テーブル構造
CREATE TABLE departments (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name TEXT NOT NULL,
department_id INT,
CONSTRAINT fk_employees_department
FOREIGN KEY (department_id) REFERENCES departments(id)
);
INSERT INTO departments (id, name) VALUES
(1, '営業'),
(2, '開発'),
(3, '人事');
INSERT INTO employees (id, name, department_id) VALUES
(101, '田中', 1),
(102, '佐藤', 2),
(103, '鈴木', 2),
(104, '高橋', 3);
期待される出力
| department | member_count |
|---|---|
| 開発 | 2 |
| 人事 | 1 |
| 営業 | 1 |