外部キー制約について
はじめに
今回は「外部キー制約」の内容を勉強したのでまとめます。
Postgre SQL 10.4 を使用しています。
外部キー制約
テーブル間でデータを維持するための制約です。
構文
FOREIGN KEY (カラム名[, …])
REFERENCE 参照するテーブル [(参照するカラム [, …])]
※[ ]内は省略可能
外部キーで制約で参照される側のカラムには主キー制約(UNIQUE)もしくは一意制約(PRIMARY KEY)が設定されている必要があります。
デフォルトでの動作
参照テーブル → 外部キーを参照する側のテーブル
被参照テーブル → 外部キーとして参照される側のテーブル
①被参照テーブルに存在しない値を持つ行を参照テーブルには挿入できない
②参照テーブルでは、被参照テーブルに存在しない値を持つ行の更新はできない
③被参照テーブルの行が参照テーブルから参照されている場合、被参照テーブルのその行は更新、削除できない
動作確認
外部キー制約のデフォルトでの動作を確認してみます。
まずは被参照テーブルとなる商品テーブル products を作成します。
CREATE TABLE products ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, product_price INTEGER NOT NULL );
商品となる カメラ を追加しておきます。
INSERT INTO products(product_id, product_name, product_price) VALUES(1, 'camera', 30000);
次に参照テーブルとなる売上テーブル sales を作成します。
sales の product_id は products の product_id を外部キーとします。
CREATE TABLE sales ( sales_id INTEGER PRIMARY KEY, product_id INTEGER REFERENCES products(product_id) );
カメラを売り上げたとして salses の product_id に 1 を追加しておきます。
INSERT INTO sales(sales_id, product_id) VALUES(1,1);
①被参照テーブルに存在しない値を持つ行を参照テーブルには挿入できない
products に存在しない product_id が 2 となる商品を sales へ追加するとエラーになります。
test=> INSERT INTO sales(sales_id, product_id) VALUES(2,2); ERROR: insert or update on table "sales" violates foreign key constraint "sales_product_id_fkey" DETAIL: Key (product_id)=(2) is not present in table "products".
②参照テーブルでは、被参照テーブルに存在しない値を持つ行の更新はできない
products に存在しない商品である 2 に sales の product_id を更新するとエラーになります。
UPDATE sales SET product_id = 2 WHERE sales_id = 1; ERROR: insert or update on table "sales" violates foreign key constraint "sales_product_id_fkey" DETAIL: Key (product_id)=(2) is not present in table "products".
③被参照テーブルの行が参照テーブルから参照されている場合、被参照テーブルのその行は更新、削除できない
sales から参照されている products の product_id を 1 から 2 に更新するとエラーになります。
UPDATE products SET product_id = 2 WHERE product_id = 1; ERROR: update or delete on table "products" violates foreign key constraint "sales_product_id_fkey" on table "sales" DETAIL: Key (product_id)=(1) is still referenced from table "sales".
sales から参照されている products の product_id が 1 のレコードを削除するとエラーになります 。
DELETE FROM products WHERE product_id = 1; ERROR: update or delete on table "products" violates foreign key constraint "sales_product_id_fkey" on table "sales" DETAIL: Key (product_id)=(1) is still referenced from table "sales".
sales の product_id が 1 のレコードをすべて削除すると products の product_id を削除または更新することが出来ます。
CASCADE オプション
CASCADEオプションを使用することで、テーブル間の整合性を効率的に保てます。
①ON DELETE CASCADE
被参照テーブルの行を削除すると、参照テーブルの該当行も同時に削除されます。
②ON UPDATE CASCADE
被参照テーブルの行を削除すると、参照テーブルの該当行も同時に削除されます。
先ほど使用していた sales に CASCADE オプションを追加してみます。
外部キー制約の更新を行うSQL文は Postgre SQL 10.4 でも見つからなかったため以下の手順を踏みます。
1.既存の制約を削除
2.改めて制約を追加
▼ tratail に同様の質問がありました
teratail.com
1.既存の制約を削除
ALTER TABLE COMMENT DROP CONSTRAINT sales_product_id_fkey;
削除する際に制約の名前を確認したい場合は \d [テーブル名] で確認できます。
\d sales
テーブル "public.sales"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト
------------+---------+----------+---------------+------------
sales_id | integer | | not null |
product_id | integer | | |
インデックス:
"sales_pkey" PRIMARY KEY, btree (sales_id)
外部キー制約:
"sales_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
2.改めて制約を追加
ALTER TABLE sales ADD FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE ON UPDATE CASCADE;
CASCADEオプションを追加した時の動作を確認してみます。
products の product_id を 1 から 2 に更新を行うと、sales の product_id も同様に更新されます。
postgresql=> SELECT * FROM sales; sales_id | product_id ----------+------------ 1 | 1 (1 行) postgresql=> SELECT * FROM products; product_id | product_name | product_price ------------+--------------+--------------- 1 | camera | 30000 (1 行) UPDATE products SET product_id = 2 WHERE product_id = 1; UPDATE 1 postgresql=> SELECT * FROM sales; sales_id | product_id ----------+------------ 1 | 2 (1 行) postgresql=> SELECT * FROM products; product_id | product_name | product_price ------------+--------------+--------------- 2 | camera | 30000 (1 行)
products の product_id が 2 のレコードを削除すると、sales の product_id も同様に削除されます。
postgresql=> SELECT * FROM sales; sales_id | product_id ----------+------------ 1 | 2 (1 行) postgresql=> SELECT * FROM products; product_id | product_name | product_price ------------+--------------+--------------- 2 | camera | 30000 (1 行) salesproduct=> DELETE FROM products WHERE product_id = 2; DELETE 1 salesproduct=> select * from products; product_id | product_name | product_price ------------+--------------+--------------- (0 行) salesproduct=> select * from sales; sales_id | product_id ----------+------------ (0 行)
.
テーブルの削除
被参照テーブルを削除する場合にはCASCADEオプションが必要になります。
CASCADEオプションを付けずに sales を削除するとエラーになります。
postgresql=> DROP TABLE products; ERROR: cannot drop table products because other objects depend on it DETAIL: constraint sales_product_id_fkey1 on table sales depends on table products HINT: Use DROP ... CASCADE to drop the dependent objects too.
CASCADEオプションを付けると products を削除することができます。
postgresql=> DROP TABLE products CASCADE ; NOTICE: drop cascades to constraint sales_product_id_fkey on table sales DROP TABLE postgresql=> \d リレーション一覧 スキーマ | 名前 | 型 | 所有者 ----------+-------+----------+-------- public | sales | テーブル | postgresql (1 行) postgresql=> select * from sales; sales_id | product_id ----------+------------ 1 | 1 (1 行) postgresq=> \d sales テーブル "public.sales" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト ------------+---------+----------+---------------+------------ sales_id | integer | | not null | product_id | integer | | | インデックス: "sales_pkey" PRIMARY KEY, btree (sales_id)
products の product_id に ON DELETE CASCADE オプションを付けていても、テーブルを削除した場合には sales に product_id が残ります。
これは sales の外部キー制約のみが削除されるためです。
外部キー制約によって参照されている場合は、外部キー制約のみが削除され、その外部キーを持つテーブルそのものは削除されません。
引用元 : Postgre SQL 10.4 文書 SQLコマンド DROP TABLE
https://www.postgresql.jp/document/10/html/sql-droptable.html
