![[アップデート] Aurora DSQL が外部キー制約をサポートしたので確認してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-7e1a533ff5b5a6a8eb5d4ae82bacdcd6/5845157a3c941213d245ec7cdf7358e8/amazon-aurora?w=3840&fm=webp)
[アップデート] Aurora DSQL が外部キー制約をサポートしたので確認してみた
いわさです。
Aurora DSQL は分散アーキテクチャの都合で、PostgreSQL の機能のうちいくつかがサポート対象外になっています。
外部キー制約もそのひとつで、以前 Aurora DSQL の制約をまとめた記事でも「Unsupported constraints」として外部キーを挙げていました。
先日のアップデートで、この外部キー制約が Aurora DSQL でも使えるようになりました。
新規・既存テーブルのどちらにも追加でき、参照先の行が削除・更新されたときの参照アクションも一通り選べるようです。
東京・大阪リージョンでも利用可能とのことです。
今回こちらを確認しました。
実際に確認してみる
東京リージョンの Aurora DSQL クラスターに psql で接続して試していきます。
住所テーブル（親）を参照する顧客テーブル（子）を、列制約の REFERENCES で定義してみます。
CREATE TABLE address (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
city TEXT NOT NULL
);
CREATE TABLE customer (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
address_id UUID REFERENCES address(id)
);
問題なく作成できました。
\d で確認すると、外部キー制約がちゃんと登録されていますね。
Table "public.customer"
Column | Type | Collation | Nullable | Default
------------+------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
name | text | | |
address_id | uuid | | |
Indexes:
"customer_pkey" PRIMARY KEY, btree_index (id) INCLUDE (name, address_id)
Foreign-key constraints:
"customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(id)
参照整合性がどう働くか確認します。
存在する住所を1件登録し、それを参照する顧客と、存在しない住所を参照する顧客をそれぞれ INSERT します。
INSERT INTO address (id, city) VALUES ('11111111-1111-1111-1111-111111111111', '東京');
INSERT INTO customer (name, address_id) VALUES ('岩田', '11111111-1111-1111-1111-111111111111');
INSERT INTO customer (name, address_id) VALUES ('架空太郎', '99999999-9999-9999-9999-999999999999');
INSERT 0 1
INSERT 0 1
ERROR: insert or update on table "customer" violates foreign key constraint "customer_address_id_fkey"
DETAIL: Key (address_id)=(99999999-9999-9999-9999-999999999999) is not present in table "address".
存在する住所を参照する INSERT は成功し、存在しない住所を参照する INSERT はエラーになりました。
DB 側で参照整合性を弾いてくれます。
参照されている親行を消そうとするとどうなるかも確認します。
参照アクションを指定していないので、デフォルトの NO ACTION が適用されるはずです。
DELETE FROM address WHERE id = '11111111-1111-1111-1111-111111111111';
ERROR: update or delete on table "address" violates foreign key constraint "customer_address_id_fkey" on table "customer"
DETAIL: Key (id)=(11111111-1111-1111-1111-111111111111) is still referenced from table "customer".
顧客から参照されている住所は削除できませんでした。
デフォルトの NO ACTION が適用されています。
次は削除時の挙動を変えます。
テーブル制約の FOREIGN KEY 構文で ON DELETE CASCADE を指定すると、親行が削除されたときに、それを参照している子行も一緒に削除されます。注文テーブルを親、注文明細テーブルを子にしました。
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
total INT
);
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID,
item_name TEXT,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
注文を1件と、それにぶら下がる明細を2件登録してから、親の注文を削除してみます。
INSERT INTO orders (id, total) VALUES ('22222222-2222-2222-2222-222222222222', 1000);
INSERT INTO order_items (order_id, item_name) VALUES ('22222222-2222-2222-2222-222222222222', 'りんご');
INSERT INTO order_items (order_id, item_name) VALUES ('22222222-2222-2222-2222-222222222222', 'みかん');
DELETE FROM orders WHERE id = '22222222-2222-2222-2222-222222222222';
SELECT count(*) FROM order_items;
DELETE 1
count
-------
0
(1 row)
親の注文を消したら、子の明細も一緒に消えました。
ON DELETE SET NULL も試したところ、こちらは親を消すと子の参照列が NULL になり、どちらも PostgreSQL と同じ挙動でした。
既存テーブルにも外部キーを追加できるとのことなので、こちらも試します。
外部キーなしで作ったテーブルに、後から ALTER TABLE ADD CONSTRAINT で追加してみます。
CREATE TABLE dept (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT
);
CREATE TABLE emp (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT,
dept_id UUID
);
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id);
ERROR: unsupported ALTER TABLE ADD CONSTRAINT statement
そのままだとエラーになりました。
既存テーブルに追加できるはずなのに、と思って ALTER TABLE のサポート構文を確認したところ、ADD table_constraint NOT VALID という形式が用意されていました。
NOT VALID を付けて再実行します。
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id) NOT VALID;
ALTER TABLE
今度は成功しました。
\d emp で見ると NOT VALID 付きで外部キーが登録されています。
Foreign-key constraints:
"emp_dept_fk" FOREIGN KEY (dept_id) REFERENCES dept(id) NOT VALID
NOT VALID は PostgreSQL では、既存行のチェックを省いて制約を追加し、後から VALIDATE CONSTRAINT で既存行を検証する、という2段階運用のための構文です。
Aurora DSQL の既存テーブルへの追加はこの NOT VALID 前提になっているようです。
ただ、その VALIDATE CONSTRAINT は Aurora DSQL では使えませんでした。
ALTER TABLE emp VALIDATE CONSTRAINT emp_dept_fk;
ERROR: unsupported ALTER TABLE VALIDATE CONSTRAINT statement
NOT VALID で外部キーを足しても、既存行をまとめて検証し直す手段は今のところ無いようです。
なお NOT VALID の状態でも、新しく入る行には制約がかかりました。既存データの不整合は、外部キーを追加する前にクエリなどで確認しておく必要がありそうです。
CASCADE を使うときは Aurora DSQL 特有の注意点があります。
Aurora DSQL は1トランザクションで変更できる行数が 3,000 行までに制限されており、CASCADE で自動的に削除される子行もこの行数にカウントされます。
通常の PostgreSQL なら子が何件あろうと削除できるので、同じ感覚で CASCADE を指定していると、子が増えたときに親の削除が急に失敗する可能性があります。公式ドキュメントでも、子行の件数が読めない関係では NO ACTION か RESTRICT が推奨されていました。
さいごに
本日は Aurora DSQL が外部キー制約に対応したので確認してみました。
これまでアプリケーション側で担保していた参照整合性を、データベースに任せられるようになりました。
一方で、既存テーブルへの追加が NOT VALID 前提だったり、CASCADE がトランザクションの行数制限に絡んだりと、PostgreSQL そのままの感覚だと引っかかる部分もあります。参照アクションの選び方は設計時に意識しておくとよさそうです。
