本記事は 2026 年 10 月 1 日 に公開された「 Working with foreign key constraints in Aurora DSQL 」を翻訳したものです。 Amazon Aurora DSQL は外部キー制約をサポートしているため、アプリケーションの参照整合性をデータベース側で直接保証できます。制約の適用方式は、分散型かつロックを使わないワークロード向けに最適化されています。 PostgreSQL では、外部キーのチェック時に参照先の行に対して FOR KEY SHARE ロックを取得します。そのため、キー列を変更したり行を削除したりするトランザクションは、その行を参照中のトランザクションが終わるまで待たされます。Aurora DSQL は、トランザクションの実行中にストレージのスナップショットに対して外部キー制約をチェックし、同時実行による競合をコミット時に解決するため、行レベルのブロッキングが発生しません。同時実行性やスループットを犠牲にせずに、参照整合性を保証できます。 本記事では、Aurora DSQL での外部キー制約の定義、即時チェックと遅延チェックの設定、ダウンタイムなしで既存テーブルに制約を追加する方法、楽観的同時実行制御 (OCC) で同時実行時の競合を解決する仕組みを説明します。また、スケールしても安全な外部キー (FK) 関係の設計についても取り上げます。EC サイトの注文パイプラインやデータ移行ワークフローといった実践的な例に加えて、OCC の競合に対するアプリケーション側のリトライロジックの指針も紹介します。 ソリューションの概要 Aurora DSQL は、外部キー制約を分散環境向けの検証の仕組みとして実装しています。外部キー関係にかかわるデータをトランザクションが変更すると、Aurora DSQL はステートメントの実行時にスナップショットベースの検証を行い (即時チェックの制約の場合)、コミット時に最終的な判定を行います。コミット済みのトランザクションが外部キー制約に違反することはなく、チェックの実行中にトランザクション同士がブロックし合うこともありません。 Aurora DSQL の外部キーサポートの主な特徴: PostgreSQL 互換の構文: 標準の REFERENCES 句と FOREIGN KEY 句を使用できます。すべての参照アクション (NO ACTION、RESTRICT、CASCADE、SET NULL、SET DEFAULT)、両方のマッチタイプ (デフォルトの MATCH SIMPLE と MATCH FULL)、遅延可能な制約をサポートしています。 OCC ベースの競合検出: 行レベルのブロッキングはありません。参照整合性はステートメントの実行時にスナップショットに対して検証され、競合は楽観的同時実行制御によってコミット時に解決されます。 キー以外の列の更新に対する最適化: Aurora DSQL は、暗黙的な KEY SHARE によって参照先のキー列を追跡します。同時に実行される FK チェックと競合するのは、キー列の変更だけです。 非同期の ALTER TABLE: NOT VALID を指定して既存テーブルに外部キーを追加し、 ALTER TABLE ASYNC ... VALIDATE CONSTRAINT で非同期に検証できます。ダウンタイムなしでスキーマを変更できます。 重要: カスケードアクション (CASCADE、SET NULL、SET DEFAULT) は、参照先の行が更新または削除されたときに、参照元テーブルの行を自動的に変更します。カスケードアクションには Aurora DSQL のトランザクションあたりの行数制限が適用されるため、注意して使わないと予期しない失敗の原因になります。子行の数に上限がない、または予測できない外部キー関係では、NO ACTION または RESTRICT を使用してください。詳細については、Aurora DSQL ユーザーガイドの「Aurora DSQL のデータベースの制限」を参照してください。 前提条件 本記事の手順を試すには、以下が必要です。 AWS アカウントの要件: Aurora DSQL クラスターが稼働している有効な AWS アカウント Aurora DSQL では、すべての接続で Transport Layer Security (TLS) による暗号化が必要です。安全に接続するには、クライアントシステムが Amazon Root Certificate Authority (Amazon Root CA 1) を信頼している必要があります。 必要な権限: 外部キー制約付きのテーブルを作成するための、スキーマに対する CREATE 権限 既存テーブルに制約を追加するための ALTER 権限 制約を削除するための DROP 権限 必要なツールとソフトウェア: PostgreSQL 互換のクライアント (psql、DBeaver、またはアプリケーションのデータベースドライバー) Aurora DSQL エンドポイントに接続できるように設定された SQL クライアント 外部キー制約付きのテーブルを作成する 外部キーは、標準の PostgreSQL 構文で、列へのインライン指定またはテーブルレベルの制約として定義します。 列制約のインライン構文 単一列の外部キーでは、列定義に REFERENCES キーワードを直接指定します。 CREATE TABLE customers ( customer_id INT PRIMARY KEY, name TEXT NOT NULL, email VARCHAR(255) UNIQUE ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE DEFAULT CURRENT_DATE, total_amount DECIMAL(10,2) ); 列リストを省略すると、Aurora DSQL は参照先テーブルの主キーを使用します。 CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT REFERENCES customers, order_date DATE DEFAULT CURRENT_DATE, total_amount DECIMAL(10,2) ); 名前付きのテーブルレベル制約の構文 複合外部キーや遅延可能な制約が必要な場合は、CONSTRAINT … FOREIGN KEY 構文を使用します。 CREATE TABLE products ( product_no INTEGER PRIMARY KEY, name TEXT UNIQUE, price NUMERIC ); CREATE TABLE order_items ( order_id INTEGER PRIMARY KEY, product_no INTEGER, quantity INTEGER, CONSTRAINT fk_product FOREIGN KEY (product_no) REFERENCES products (product_no) DEFERRABLE INITIALLY IMMEDIATE ); 複合外部キーの例 複合主キーや複合一意制約を参照する場合、外部キーを複数の列にまたがって定義できます。 CREATE TABLE inventory ( warehouse_id INTEGER, product_no INTEGER, quantity INTEGER, PRIMARY KEY (warehouse_id, product_no) ); CREATE TABLE shipments ( shipment_id INTEGER PRIMARY KEY, warehouse_id INTEGER, product_no INTEGER, FOREIGN KEY (warehouse_id, product_no) REFERENCES inventory (warehouse_id, product_no) ); 自己参照の外部キー 外部キー制約では、制約が属するテーブル自体を参照することもできます。たとえば、ツリー構造は次のように表現できます。 CREATE TABLE tree ( node_id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES tree, name TEXT ); 最上位のノードは parent_id が NULL になります。parent_id が NULL でない行は、テーブル内の有効な行を参照するよう制約されます。 参照アクションの例 参照先の行が削除または更新されたときの動作を指定できます。 CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, product_no INTEGER REFERENCES products ON DELETE RESTRICT, quantity INTEGER ); RESTRICT を指定すると、注文から参照されている製品を削除しようとした時点ですぐにエラーになります。デフォルトの NO ACTION では、制約が DEFERRABLE として宣言されていれば、チェックをトランザクションの終了時まで遅延できます。 既存テーブルに外部キーを追加する 既存テーブルには、ALTER TABLE … ADD CONSTRAINT … NOT VALID で外部キー制約を追加できます。NOT VALID オプションを指定すると、既存データを検証せずに制約を追加するため、データ定義言語 (DDL) の実行時にテーブル全体をスキャンせずに済みます。 ALTER TABLE order_items ADD CONSTRAINT fk_product FOREIGN KEY (product_no) REFERENCES products (product_no) NOT VALID; 制約の追加後は、新しいデータ操作言語 (DML) の操作が制約に対して検証されます。書き込みをブロックせずに既存データを非同期で検証するには、次のようにします。 ALTER TABLE ASYNC order_items VALIDATE CONSTRAINT fk_product; 既存の制約の遅延可否を変更することもできます。 ALTER TABLE order_items ALTER CONSTRAINT fk_product DEFERRABLE INITIALLY DEFERRED; 外部キー制約を管理する 設定した制約は、削除したり内容を確認したりできます。 制約を削除する テーブルから外部キー制約を削除するには、次のようにします。 ALTER TABLE order_items DROP CONSTRAINT fk_product; 外部キー制約を確認する システムカタログをクエリして、外部キーの定義を確認します。 SELECT conname AS constraint_name, conrelid::regclass AS child_table, confrelid::regclass AS parent_table, pg_get_constraintdef(oid) AS definition FROM pg_constraint WHERE contype = 'f'; 制約の即時チェック 制約のタイミングが即時 (デフォルト) に設定されている場合、Aurora DSQL はステートメントの実行時にトランザクションのスナップショットをクエリして参照整合性を検証します。違反はその場で拒否されます。 有効な挿入は成功する INSERT INTO products VALUES (1, 'Laptop', 999.99); INSERT INTO products VALUES (2, 'Mouse', 29.99); INSERT INTO products VALUES (3, 'Keyboard', 79.99); -- Valid order referencing an existing product succeeds. INSERT INTO order_items VALUES (100, 1, 2); 存在しない親を参照するとすぐに失敗する -- Order for non-existent product fails immediately. INSERT INTO order_items VALUES (101, 999, 1); -- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product" 参照されている親行の削除は失敗する DELETE FROM products WHERE product_no = 1; -- ERROR: update or delete on table "products" violates foreign key constraint "fk_product" on table "order_items" 子行が孤立するような親の主キーの更新は失敗する UPDATE products SET product_no = 99 WHERE product_no = 1; -- ERROR: update or delete on table "products" violates foreign key constraint "fk_product" on table "order_items" 参照されていない親行は削除できる -- Product 3 has no orders referencing it. DELETE FROM products WHERE product_no = 3; -- OK 制約の遅延チェック 制約が遅延可能として定義されていれば、トランザクション内で SET CONSTRAINTS を使って遅延チェックに切り替えられます。遅延させると検証はコミット時に移るため、親より先に子行を挿入し、コミットまでに制約を満たせば問題ありません。データのロード時や、複数のステートメントからなる複雑なトランザクションで役立ちます。 注: Aurora DSQL では、SET CONSTRAINTS は外部キー制約にのみ適用されます。主キー、一意、NOT NULL、CHECK の各制約は常に即時チェックされ、遅延できません。 参照先の行より先に参照元の行を挿入する BEGIN; SET CONSTRAINTS fk_product DEFERRED; INSERT INTO order_items VALUES (200, 80, 1); -- product 80 doesn't exist yet INSERT INTO products VALUES (80, 'Monitor', 349.99); COMMIT; -- OK: constraint satisfied at commit time コミット時の遅延チェックでの違反 COMMIT までに違反が解消されない場合、トランザクション全体がロールバックされます。 BEGIN; SET CONSTRAINTS fk_product DEFERRED; INSERT INTO order_items VALUES (201, 777, 1); -- product 777 never created COMMIT; -- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product" -- entire transaction rolled back すべての外部キー制約をまとめて遅延させる SET CONSTRAINTS ALL DEFERRED を実行すると、トランザクション内の遅延可能な外部キー制約がすべて遅延されます。 BEGIN; SET CONSTRAINTS ALL DEFERRED; INSERT INTO order_items VALUES (202, 90, 1); INSERT INTO products VALUES (90, 'Speakers', 129.99); COMMIT; -- OK トランザクションの途中で即時チェックに戻す トランザクション内で制約を IMMEDIATE に戻すと、スナップショットに対する検証が強制的に実行されます。Aurora DSQL は、遅延していた未チェックのデータ変更をその時点でチェックします。 BEGIN; SET CONSTRAINTS fk_product DEFERRED; INSERT INTO order_items VALUES (203, 888, 1); -- no error yet (deferred) SET CONSTRAINTS fk_product IMMEDIATE; -- triggers check NOW -- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product" ROLLBACK; 同時実行と OCC の動作 Aurora DSQL は、ロックを使わない同時実行制御の仕組みである OCC を採用しています。そのため、従来の PostgreSQL 環境でよく見られる行レベルのブロッキング、デッドロック、ロック待ちのタイムアウトが発生しません。行の読み書き時にロックを取得する代わりに、各トランザクションは開始時点で取得したデータベースの一貫したスナップショットに対して処理を行います。複数のトランザクションが互いを待つことなく、同じ行を同時に読み書きできます。同時に実行された 2 つのトランザクションが同じデータに競合する形でアクセスしたかどうかは、コミット時に初めて Aurora DSQL のアジュディケータが判定します。競合が見つかると、一方のトランザクションは成功し、もう一方はシリアル化エラー ( SQLSTATE 40001 ) で失敗します。アプリケーションは失敗したトランザクションをリトライできます。 Aurora DSQL は、2 つのステップで参照整合性を維持します。 スナップショットによる検証 : すべてのトランザクションは、開始時点で取得した一貫したスナップショットに対して実行されます。参照元の行を挿入すると、Aurora DSQL はそのスナップショット時点の参照先テーブルを読み取り、参照先のキーが存在することを確認します。参照先のキーを削除または更新すると、Aurora DSQL はそのスナップショット時点の参照元テーブルを読み取ります。RESTRICT の場合は参照元の行が存在しないこと、NO ACTION の場合は操作によって孤立する行が残らないことを確認します。 コミット時の競合解決 : スナップショットによる検証で分かるのは、トランザクションの開始時点で制約が満たされていたことだけです。その後、同時に実行された別のトランザクションが参照先のデータを変更している可能性があります。Aurora DSQL はコミット時に、同時実行中のトランザクションが自分のトランザクションの参照整合性に影響を与えたかどうかを検出し、競合があれば OCC エラーでトランザクションを失敗させます。 重要: 参照先または参照元のテーブルに対する DML はすべて、参照整合性を維持するための追加の読み取りを伴います。テーブルに外部キー制約を追加する前に、ワークロードのベンチマークを行い、性能特性が想定どおりであることを確認してください。 -- Seed the products used by the concurrency examples INSERT INTO products VALUES (4, 'Cable', 9.99); INSERT INTO products VALUES (5, 'Dock', 199.99); INSERT INTO products VALUES (6, 'Charger', 19.99); 親の DELETE と子の INSERT の同時実行: 競合する -- Session A: BEGIN; DELETE FROM products WHERE product_no = 4; COMMIT; -- succeeds -- Session B (started concurrently, before Session A committed): BEGIN; INSERT INTO order_items VALUES (103, 4, 1); COMMIT; -- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001) 2 つのセッションは同時に実行されます。Aurora DSQL はコミット時に競合を解決するため、削除済みの製品を指す注文が残ることはありません。 親の主キーの UPDATE と子の INSERT の同時実行: 競合する -- Session A: BEGIN; UPDATE products SET product_no = 50 WHERE product_no = 5; COMMIT; -- Session B (concurrent): BEGIN; INSERT INTO order_items VALUES (104, 5, 1); COMMIT; -- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001) キー以外の列の更新が競合しない理由 競合を判定するために、Aurora DSQL は参照先の行に対して暗黙的に KEY SHARE の追跡を適用します。同時に実行中の外部キーチェックを無効にするのは、キー列 (PRIMARY KEY の列、または部分インデックスや式インデックスではない UNIQUE インデックスの列) の変更だけです。製品の価格のようなキー以外の列を更新しても、その製品を参照する新しい子行の挿入とは競合しません。 キー以外の列 (price) の更新: 競合しない -- Session A: BEGIN; UPDATE products SET price = 29.99 WHERE product_no = 6; COMMIT; -- succeeds -- Session B (concurrent): BEGIN; INSERT INTO order_items VALUES (105, 6, 1); COMMIT; -- succeeds -- Updating price (a non-key column) doesn't conflict with the foreign key on product_no. キー列 (UNIQUE インデックスを持つ name) の更新: 競合する -- Session A: BEGIN; UPDATE products SET name = 'Fast Charger' WHERE product_no = 6; COMMIT; -- Session B (concurrent): BEGIN; INSERT INTO order_items VALUES (108, 6, 1); COMMIT; -- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001) products テーブルの name は TEXT UNIQUE として宣言されたキー列なので、競合が発生します。 同じ親に対する複数の子行の挿入: 誤った競合は起きない 同じ親を参照する子行を 2 つのトランザクションが同時に挿入しても、互いに競合しません。 -- Session A: BEGIN; INSERT INTO order_items VALUES (106, 2, 1); COMMIT; -- Session B (concurrent): BEGIN; INSERT INTO order_items VALUES (107, 2, 2); COMMIT; -- OK: both succeed 人気のある親行 (売れ筋の製品など) に対して数千件の子行の挿入が同時に行われても、競合は発生しません。 スケールを考慮した設計 注: ここから先の例では、前のセクションで作成したテーブルとは別の、UUID ベースのスキーマを使用します。実際に試す場合は、先に前のテーブルを削除するか、新しいセッションを使用してください。 従来のデータベースでは、上限のない一対多 (1:M) の外部キー関係があると、テーブルロック、インデックスの肥大化、削除の遅延といったリスクがあります。Aurora DSQL では OCC によってロックのリスクはなくなりますが、性能とコストのリスクは残ります。さらに、Aurora DSQL ではトランザクションで変更できる行数が 3,000 行に制限されているため、CASCADE アクションがかかわる場合は特に注意が必要です。 外部キーを追加する前に、その関係が今後どのようにスケールするかを検討してください。 スケールしても問題にならない関係 次の関係は件数が自然に抑えられるため、規模が大きくなっても問題になりません。 厳密な一対一 (1:1): 子テーブルが UNIQUE な外部キーで親を参照します。子が親より大きくなることはありません (例: user_id を使った users → user_profiles)。 多対一の参照: 数百万の行が、静的またはゆっくりとしか増えない設定テーブルを参照します。増えるのは「多」の側で、親の件数は抑えられたままです (例: 小さな countries テーブルを参照する数百万のユーザー)。子行を挿入するたびに親行に対する検証が行われます。親のキー以外の列 (国の display_name など) の更新は競合せずに処理されます。ただし、親のキー列 (参照される country_id など) を更新すると、同時に実行中の子行の挿入と競合します。そのため、頻繁に参照される参照用の行のキー列は、事実上変更できなくなります。通常はこれが望ましい動作です。参照テーブルのキーは安定しているべきで、変更が必要な属性はキー以外の列に持たせます。 上限のある一対多: 子テーブルは親 1 件に対して複数の行を持てますが、現実的な制約によって件数は少なく抑えられます (例: orders → order_items。一般的なショッピングカートの商品数は数十件程度です)。削除の影響を受ける子行の数に上限があるため、この場合は CASCADE を安全に使えます。 規模が爆発的に大きくなるリスクのある関係 問題を引き起こすのは次のような関係です。子の側に上限がなく、1 つの親行に時間とともに数百万の子行が蓄積されることがあります。 グローバルな「何でも紐付ける」アンカー: 大量のログ、監査、イベントデータを、1 つのテナントやユーザーに直接紐付けるケースです (例: 利用の多い法人アカウントが audit_logs テーブルの数十億行のアンカーになる)。Aurora DSQL では、この親に対する CASCADE 削除はトランザクションの行数制限を超えて失敗します。 急激に拡散するエンゲージメント機能: ユーザーのアクティビティを 1 つのコンテンツに直接紐付けるケースです。1 件の投稿が話題になると post_likes に数百万行が蓄積されることがあり、その投稿に対する CASCADE 削除はトランザクションの行数制限を超えます (例: posts の人気のある 1 行が、突然 post_likes の 10,000,000 行のアンカーになる)。子行を挿入するたびに、その 1 つの親行に対して FK 検証の読み取りが発生します。量が多くなると、無視できないコストになります。 大量に売れる製品: EC サイトでは、products → order_items の関係は爆発的に大きくなりがちです。1 つの人気製品 (あるいは全注文に共通する「送料」項目) が、過去の数百万件の注文行のアンカーになることがあります。CASCADE 削除を実行すると、その製品の過去の注文明細をすべて削除しようとして失敗します。 Aurora DSQL 固有の影響: Aurora DSQL では、上限のない FK 関係があると、子行の挿入や親行の更新のたびに FK 検証のための追加の読み取りコストが発生します。また、子行の同時挿入中に親のキー列が変更されると OCC の競合が発生します。 Aurora DSQL 向けのアーキテクチャ上の対策 関係に上限がなくなる可能性がある場合は、次の対策を適用します。 1. 上限のないキーには CASCADE を使わない。 件数の多い外部キー (product_id や tenant_id など) では ON DELETE CASCADE を避けてください。Aurora DSQL では、数千の子行を持つ親を削除するとトランザクションの行数制限を超えて失敗します。代わりに論理削除 ( is_active = false ) を使い、子行はバックグラウンドのトランザクションでバッチ処理してクリーンアップします。 -- AVOID: CASCADE on unbounded relationship CREATE TABLE order_items ( line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), product_id UUID REFERENCES products ON DELETE CASCADE, -- dangerous at scale quantity INTEGER ); -- PREFERRED: RESTRICT + soft-delete pattern CREATE TABLE order_items ( line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), product_id UUID REFERENCES products ON DELETE RESTRICT, quantity INTEGER ); -- Soft-delete the product instead of hard-deleting: UPDATE products SET is_active = false WHERE product_id = 'prod-xyz'; -- replace with actual UUID -- Clean up old order_items in batched transactions (≤3,000 rows each) 2. 変わりうる属性は書き込み時にスナップショットとして保存する。 注文のようなトランザクションデータでは、時間とともに変わる属性 (price や product_name など) を、トランザクションの実行時に子テーブルへ直接コピーします。親テーブルの現在の状態に頼らずに、顧客が実際に支払った金額や注文時の製品名を残せます。sku のように変わらない識別子は親テーブルにだけ持たせれば十分で、FK による参照で事足ります。 CREATE TABLE order_items ( line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID REFERENCES orders, product_id UUID REFERENCES products, quantity INTEGER NOT NULL, -- Snapshot mutable attributes at order time unit_price NUMERIC NOT NULL, product_name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() ); 3. クエリにはページネーションと期間の制限を必ず付ける。 SELECT * FROM order_items WHERE product_id = X のような範囲を限定しないクエリは許可しないでください。期間の制限やカーソルベースのフィルターを必ず使います。タイムスタンプを比較する際に両辺のデータ型をそろえると、クエリプランナーがインデックスを効果的に使えます。 -- AVOID: unbounded query without time bounds or pagination SELECT * FROM order_items WHERE product_id = 'prod-xyz'; -- replace with actual UUID -- PREFERRED: time-bounded with index support SELECT * FROM order_items WHERE product_id = 'prod-xyz' -- replace with actual UUID AND created_at >= NOW() - INTERVAL '30 days' ORDER BY created_at DESC LIMIT 100; 4. 固定の列挙値には、FK による参照ではなく CHECK 制約を使う。 カーディナリティの低いテーブル (ステータスコード、種別、カテゴリなど) を参照すると、挿入のたびに FK 検証の負荷がかかりますが、値が変わることはないため実質的なメリットはありません。 -- AVOID: FK to a 5-row lookup table CREATE TABLE task_statuses (status_id INT PRIMARY KEY, label TEXT); CREATE TABLE tasks (task_id UUID PRIMARY KEY, status_id INT REFERENCES task_statuses); -- PREFERRED: CHECK constraint (no FK overhead, no parent-row reads) CREATE TABLE tasks ( task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), status VARCHAR(20) CHECK (status IN ('open', 'in_progress', 'done', 'blocked', 'cancelled')) ); 実践的な例 ここまでのセクションでは構文と動作を説明しました。以下のシナリオでは、本番環境でそれらをどのように組み合わせるかを紹介します。 EC サイトの注文パイプライン スケールを考慮した FK 設計のスキーマ: CREATE TABLE customers ( customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, email VARCHAR(255) UNIQUE ); CREATE TABLE products ( product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sku VARCHAR(50) UNIQUE, name TEXT, price NUMERIC, is_active BOOLEAN DEFAULT true -- soft-delete instead of hard-delete ); CREATE TABLE orders ( order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), customer_id UUID REFERENCES customers, status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMPTZ DEFAULT NOW() ); CREATE TABLE order_lines ( line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID, product_id UUID REFERENCES products ON DELETE RESTRICT, -- unbounded: never CASCADE quantity INTEGER NOT NULL, -- Snapshot mutable attributes at order time unit_price NUMERIC NOT NULL, product_name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(), CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders (order_id) ON DELETE CASCADE -- bounded: safe to CASCADE DEFERRABLE INITIALLY DEFERRED ); このスキーマには、前述の「スケールを考慮した設計」の対策を適用しています。CASCADE は上限のある関係 (orders → order_lines) だけに使い、上限のない関係 (products → order_lines) には RESTRICT を使っています。また、製品データは注文時にスナップショットとして保存しています。 遅延制約を使った注文の登録: fk_order 制約は DEFERRABLE INITIALLY DEFERRED なので、親の注文行より先に注文明細を挿入できます。また、注文時の製品名と価格をスナップショットとして保存します。 -- Setup: seed the referenced parents first INSERT INTO customers (customer_id, name, email) VALUES ('d3eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'Alice', 'alice@example.com'); INSERT INTO products (product_id, sku, name, price) VALUES ('b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 'WM-100', 'Wireless Mouse', 49.99), ('c2eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 'HUB-200', 'USB-C Hub', 129.99); -- Now place an order with deferred FK on order_id BEGIN; -- Insert order lines first (fk_order to orders is deferred; product FKs are immediate -- but the products already exist above, so they pass instantly) INSERT INTO order_lines (order_id, product_id, quantity, unit_price, product_name) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 2, 49.99, 'Wireless Mouse'), ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'c2eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 1, 129.99, 'USB-C Hub'); -- Insert the order header (satisfies the deferred fk_order at commit) INSERT INTO orders (order_id, customer_id, status) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'd3eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'pending'); COMMIT; -- OK: fk_order validated at commit time; product FKs were checked immediately and passed Aurora DSQL Loader によるデータ移行 別のデータベースからデータを移行する場合は、Aurora DSQL Loader の使用をお勧めします。Aurora DSQL Loader はオープンソースの CLI で、並列化、コネクションプーリング、競合とリトライの処理、AWS Identity and Access Management (IAM) 認証を担います。Aurora DSQL では、前述の行数の制限に加えて、書き込みトランザクションあたりの変更データ量が 10 MiB、実行時間が 5 分に制限されています。Loader はこれら 3 つの制限の範囲内に収まるよう、自動的にバッチ処理します。 ステップ 1: 移行元のデータベースからデータをエクスポートする -- From PostgreSQL: COPY customers TO '/export/customers.csv' WITH (FORMAT csv, HEADER true); COPY products TO '/export/products.csv' WITH (FORMAT csv, HEADER true); COPY orders TO '/export/orders.csv' WITH (FORMAT csv, HEADER true); COPY order_lines TO '/export/order_lines.csv' WITH (FORMAT csv, HEADER true); ステップ 2: 親テーブルを先にロードし、次に子テーブルをロードする # Load parent tables first (no FK dependencies) aurora-dsql-loader load \ --endpoint cluster-id.dsql.us-east-1.on.aws \ --source-uri /export/customers.csv \ --table customers aurora-dsql-loader load \ --endpoint cluster-id.dsql.us-east-1.on.aws \ --source-uri /export/products.csv \ --table products # Load child tables after parents are loaded aurora-dsql-loader load \ --endpoint cluster-id.dsql.us-east-1.on.aws \ --source-uri /export/orders.csv \ --table orders aurora-dsql-loader load \ --endpoint cluster-id.dsql.us-east-1.on.aws \ --source-uri /export/order_lines.csv \ --table order_lines 別の方法: FK なしでロードし、後から制約を追加する ロード時間を短縮するには、ロード中は FK 制約を設定せず、後から追加します。 -- Step 1: Load all data (no FK constraints yet - maximum throughput) -- Use aurora-dsql-loader for each table in any order -- Step 2: Add constraints without validating existing data (instant DDL) ALTER TABLE order_lines ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders (order_id) NOT VALID; -- Step 3: Validate existing data asynchronously (non-blocking) ALTER TABLE ASYNC order_lines VALIDATE CONSTRAINT fk_order; -- Step 4: Monitor progress SELECT * FROM sys.jobs WHERE job_type = 'VALIDATE_CONSTRAINT'; -- Or block the session until validation completes: CALL sys.wait_for_job('<job_id>'); FK を後から追加する 2 段階の方法では、取り込み時に FK の負荷がかからないため初回ロードが速くなり、本番稼働の前に参照整合性も検証できます。 OCC の競合に対するアプリケーションのリトライロジック Aurora DSQL は OCC を使うため、競合するトランザクションはロックを待つのではなく、コミット時に SQLSTATE 40001 で失敗します。アプリケーション側でこのエラーを処理する必要があります。Aurora DSQL では、次の 2 種類の競合を区別しています。 OC000 (データの競合): 2 つのトランザクションが同じ行を変更しました。トランザクション全体をリトライしてください。 OC001 (スキーマの競合): セッションにキャッシュされたスキーマが古くなっています (同時に実行された DDL の後など)。セッションがカタログキャッシュを更新するため、通常は 1 回のリトライで成功します。 Aurora DSQL のリトライロジックの基本原則: 常にトランザクション全体をリトライする: 40001 エラーの後、トランザクションはロールバックされています。BEGIN から再実行してください。 ジッター付きのエクスポネンシャルバックオフを使う: リトライまでの待機時間にランダム性を持たせるとリトライのタイミングが分散し、次の試行で同じトランザクション同士が再び衝突する可能性を下げられます。 リトライすべきエラーを見極める: SQLSTATE 40001 は OCC の競合なので、トランザクションをリトライします。スキーマの競合 ( OC001 ) は、セッションが最新のカタログを取得するため、通常は最初のリトライで解消されます。一方、外部キー違反 ( 23503 ) は一時的な競合ではなく、データ自体の問題です。リトライしても解決しないため、アプリケーションのロジックを確認してください。 リトライ回数に上限を設け、発生率を監視する: ワークロードに合ったリトライ回数の上限を設定します。上限に達しても失敗する場合、一時的な競合ではなく、同時書き込みが集中しすぎているホットな親行が原因と考えられます。本番環境では 40001 エラーの発生率を追跡してください。発生率が継続的に上がっている場合は、スキーマの見直しが必要です (前述の「スケールを考慮した設計」を参照)。 クリーンアップ 本記事で作成したリソースを削除するには、次を実行します。 -- Drop children before parents (respecting FK dependencies) DROP TABLE IF EXISTS order_lines; DROP TABLE IF EXISTS order_items; DROP TABLE IF EXISTS shipments; DROP TABLE IF EXISTS tasks; DROP TABLE IF EXISTS task_statuses; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS inventory; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS tree; DROP TABLE IF EXISTS customers; まとめ Aurora DSQL の外部キー制約を使うと、参照整合性をアプリケーションコードではなくデータベースのレベルで保証できます。Aurora DSQL は行レベルのロックではなく OCC を使うため、同時実行されるトランザクションは互いをブロックせずに処理され、競合はコミット時に検出されます。また、キー以外の列の更新が外部キーチェックに影響することはありません。遅延チェック、制約の非同期検証、本記事で紹介したスケールを考慮した設計パターンを活用すれば、高スループットの分散ワークロードでも安心して外部キーを導入できます。詳細については、 Aurora DSQL ユーザーガイド の「 同時実行制御 」のページを参照してください。 著者について Rekha Reddy Anupati AWS のデータベーススペシャリストソリューションアーキテクトです。AWS のマネージドデータベースサービスを使った、スケーラブルなデータベースソリューションの設計と実装をお客様と共に進めています。 Arnab Chowdhury AWS のデータベーススペシャリストソリューションアーキテクトです。AWS 上でのデータベースアーキテクチャの設計、移行、最適化を支援しています。 この記事は Kiro が翻訳を担当し、Solutions Architect の Kenta Nagasue がレビューしました。