Working with foreign key constraints in Aurora DSQL

Amazon Aurora DSQL supports foreign key constraints, so you can enforce application referential integrity directly in the database with enforcement behavior optimized for distributed, lock-free workloads.

In PostgreSQL, a foreign key check takes a FOR KEY SHARE lock on the referenced row, so a transaction that modifies the key columns or deletes the row waits behind any transaction still referencing it. Aurora DSQL checks foreign key constraints against storage snapshots as the transaction runs, then resolves concurrent conflicts at commit time without row-level blocking. This gives your application the referential integrity guarantees without sacrificing concurrency or throughput.

In this post, we walk through defining foreign key constraints in Aurora DSQL, configuring immediate versus deferred enforcement, adding constraints to existing tables without downtime, and understanding how optimistic concurrency control (OCC) resolves concurrent conflicts. We also cover designing foreign key (FK) relationships that scale safely, with practical examples including an ecommerce order pipeline and a data migration workflow, plus guidance on application-level retry logic for OCC conflicts.

Solution overview

Aurora DSQL implements foreign key constraints as distributed validation primitives. When a transaction modifies data that involves a foreign key relationship, Aurora DSQL performs snapshot-based validation at statement time (for immediate constraints) and final adjudication at commit time. A committed transaction can’t violate a foreign key constraint, and no transaction blocks another while these checks run.

Key features of Aurora DSQL foreign key support:

• PostgreSQL-compatible syntax: Standard REFERENCES and FOREIGN KEY clauses with support for all referential actions (NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT), both match types (MATCH SIMPLE, the default, and MATCH FULL), and deferrable constraints.
• OCC-based conflict detection: No row-level blocking. Referential integrity is validated against snapshots at statement time, with conflicts resolved at commit time through optimistic concurrency control.
• Non-key update optimization: Aurora DSQL tracks referenced key columns through implicit KEY SHARE. Only key-column changes conflict with concurrent FK checks.
• Asynchronous ALTER TABLE: Add foreign keys to existing tables with NOT VALID and validate asynchronously using ALTER TABLE ASYNC ... VALIDATE CONSTRAINT, so you can evolve your schema with zero downtime.

Important: Cascading actions (CASCADE, SET NULL, SET DEFAULT) automatically modify rows in the referencing table when a referenced row is updated or deleted. The Aurora DSQL transaction row limit applies to these actions and can cause unexpected failures if not used carefully. Prefer NO ACTION or RESTRICT for foreign key relationships where child-row cardinality is unbounded or unpredictable. For more information, see Database limits in Aurora DSQL in the Aurora DSQL User Guide.

Prerequisites

You must have the following prerequisites to follow along with this post.

AWS account requirements:

• Active AWS account with an Aurora DSQL cluster running.
• Aurora DSQL requires all connections to use Transport Layer Security (TLS) encryption. To establish secure connections, your client system must trust the Amazon Root Certificate Authority (Amazon Root CA 1).

Required permissions:

• CREATE permission on schema for creating tables with foreign key constraints.
• ALTER permission for adding constraints to existing tables.
• DROP permission for removing constraints.

Tools and software needed:

• PostgreSQL-compatible client (psql, DBeaver, or application database driver).
• SQL client configured to connect to Aurora DSQL endpoint.

Create tables with foreign key constraints

You define foreign keys with standard PostgreSQL syntax, either inline on a column or as a table-level constraint.

Inline column constraint syntax

For single-column foreign keys, use the REFERENCES keyword directly on the column definition:

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)
);

If you omit the column list, Aurora DSQL uses the primary key of the referenced table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2)
);

Named table-level constraint syntax

When you need composite foreign keys or deferrable constraints, use the CONSTRAINT … FOREIGN KEY syntax:

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
);

Composite foreign key example

Foreign keys can span multiple columns when referencing a composite primary key or unique constraint:

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)
);

Self-referential foreign key

A foreign key constraint can reference the same table it belongs to. For example, representing a tree structure:

CREATE TABLE tree (
    node_id INTEGER PRIMARY KEY,
    parent_id INTEGER REFERENCES tree,
    name TEXT
);

A top-level node would have NULL parent_id, while non-NULL parent_id entries are constrained to reference valid rows of the table.

Referential actions example

You can specify what happens when a referenced row is deleted or updated:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_no INTEGER REFERENCES products ON DELETE RESTRICT,
    quantity INTEGER
);

With RESTRICT, attempting to delete a product that has orders referencing it produces an error immediately. With the default NO ACTION, the check can be deferred to the end of the transaction if the constraint is declared DEFERRABLE.

Add foreign keys to existing tables

You can add foreign key constraints to existing tables using ALTER TABLE … ADD CONSTRAINT … NOT VALID. The NOT VALID option adds the constraint without validating existing data, which avoids scanning the entire table at data definition language (DDL) time.

ALTER TABLE order_items
ADD CONSTRAINT fk_product
FOREIGN KEY (product_no) REFERENCES products (product_no)
NOT VALID;

After adding the constraint, new data manipulation language (DML) operations are validated against it. To validate existing data asynchronously without blocking writes:

ALTER TABLE ASYNC order_items
VALIDATE CONSTRAINT fk_product;

You can also change the deferrability of an existing constraint:

ALTER TABLE order_items
ALTER CONSTRAINT fk_product DEFERRABLE INITIALLY DEFERRED;

Manage foreign key constraints

After constraints are in place, you can drop or inspect them.

Drop a constraint

To remove a foreign key constraint from a table:

ALTER TABLE order_items DROP CONSTRAINT fk_product;

Verify foreign key constraints

Query the system catalog to inspect foreign key definitions:

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';

Immediate constraint enforcement

When a constraint is configured with immediate timing (the default), Aurora DSQL validates referential integrity at statement time by querying the transaction’s snapshot. Violations are rejected immediately.

Valid insert succeeds

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);

Referencing a non-existent parent fails immediately

-- 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"

Deleting a referenced parent row fails

DELETE FROM products WHERE product_no = 1;
-- ERROR: update or delete on table "products" violates foreign key constraint "fk_product" on table "order_items"

Updating a parent’s primary key to orphan children fails

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"

Deleting unreferenced parent rows is allowed

-- Product 3 has no orders referencing it.
DELETE FROM products WHERE product_no = 3;
-- OK

Deferred constraint enforcement

The same constraint can be toggled to deferred enforcement within a transaction using SET CONSTRAINTS, if it was defined as deferrable in the first place. Deferring moves the validation check to commit time, so you can insert child rows before their parents exist, then satisfy the constraint before committing. This is useful during data loading or complex multi-statement transactions.

Note: In Aurora DSQL, SET CONSTRAINTS applies to foreign key constraints only. Primary key, unique, NOT NULL, and CHECK constraints are always checked immediately and can’t be deferred.

Insert referencing row before referenced row

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

Deferred violation at commit time

If the violation isn’t resolved before COMMIT, the entire transaction is rolled back:

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

Defer all foreign key constraints at once

SET CONSTRAINTS ALL DEFERRED defers every deferrable foreign key constraint in the transaction:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO order_items VALUES (202, 90, 1);
INSERT INTO products VALUES (90, 'Speakers', 129.99);

COMMIT;
-- OK

Switch back to immediate mid-transaction

Flipping a constraint back to IMMEDIATE within a transaction forces a snapshot validation check. Aurora DSQL immediately checks any outstanding data modifications that would otherwise have been deferred:

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;

Concurrency and OCC behavior

Aurora DSQL uses OCC, a lock-free concurrency mechanism that eliminates the row-level blocking, deadlocks, and lock-wait timeouts common in traditional PostgreSQL deployments. Instead of acquiring locks when reading or writing rows, each transaction operates against a consistent snapshot of the database taken at its start time. Multiple transactions can read and write the same rows concurrently without waiting on each other. Only at commit time does the Aurora DSQL adjudicator evaluate whether any two concurrent transactions touched the same data in a conflicting way. If a conflict is found, one transaction succeeds and the other fails with a serialization error (SQLSTATE 40001), which the application can then retry.

Aurora DSQL maintains referential integrity in two steps:

1. Snapshot validation: Every transaction runs against a consistent snapshot taken at its start time. When you insert a referencing row, Aurora DSQL reads the referenced table at that snapshot to confirm the referenced key exists. When you delete or update a referenced key, Aurora DSQL reads the referencing table at that snapshot. This confirms that no referencing rows exist for RESTRICT, or that the operation leaves no orphaned rows for NO ACTION.
2. Commit-time conflict resolution: Snapshot validation proves that the constraint held at the transaction’s start time, but a concurrent transaction could have changed the referenced data since then. At commit time, Aurora DSQL detects whether any concurrent transaction affected your transaction’s referential integrity and fails the transaction with an OCC error if a conflict is found.

Important: All DML on referenced or referencing tables incurs extra reads to maintain referential integrity. Before adding a foreign key constraint to a table, make sure you benchmark the workload and validate the performance characteristics are in line with expectations.

-- 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);

Concurrent DELETE parent and INSERT child: Conflict

-- 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)

Both sessions run at the same time. Aurora DSQL resolves the conflict at commit. You can’t end up with an order that points to a deleted product.

Concurrent UPDATE parent PK and INSERT child: Conflict

-- 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)

Why non-key column updates don’t conflict

To adjudicate conflicts, Aurora DSQL implicitly applies KEY SHARE tracking to referenced rows. Only changes to key columns (columns in a PRIMARY KEY or members of a UNIQUE, non-partial, non-expression index) invalidate a concurrent foreign key check. Updates to non-key columns, like adjusting a product’s price, proceed without conflicting with new child inserts for that product.

Non-key column update (price): no conflict:

-- 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.

Key column update (name with UNIQUE index): conflict:

-- 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)

Note that name conflicts here because the products table declares it as TEXT UNIQUE, making it a key column.

Multiple child inserts on the same parent: No false conflicts

Two concurrent transactions inserting child rows that reference the same parent don’t conflict with each other:

-- 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

Popular parent rows (like a best-selling product) can receive thousands of concurrent child inserts without any of them conflicting.

Designing for scale

Note: The examples from here on use a UUID-based schema that is independent of the tables created in earlier sections. If you want to run them, drop the earlier tables first or use a fresh session.

In traditional databases, unbounded One-to-Many (1:M) foreign key relationships risk table locks, massive index growth, and slow deletes. In Aurora DSQL, the lock risk is eliminated by OCC. But the performance and cost risks remain, and become even more critical when CASCADE actions are involved, because Aurora DSQL enforces a 3,000-row transaction mutation limit.

Before adding a foreign key, think about how the relationship will scale over time.

Scale-resistant relationships

These relationships are naturally capped and won’t cause problems at scale:

• Strict One-to-One (1:1): A child table references a parent through a UNIQUE foreign key. The child can never grow larger than the parent. (for example, users → user_profiles using user_id).
• Many-to-One lookups: Millions of rows reference a static or slow-growing configuration table. The “Many” side drives growth, keeping the parent capped. (for example, millions of users referencing a small countries table). Every child insert validates against the parent row. Updates to non-key columns on the parent (such as a country’s display_name) proceed without conflict. However, updates to the parent’s key columns (such as the referenced country_id) will conflict with any concurrent child insert, making key columns on heavily referenced lookup rows effectively immutable. This is typically the behavior that you want: lookup table keys should be stable, and any mutable attributes should live in non-key columns.
• Bounded One-to-Many: The child table can have multiple rows per parent, but real-world constraints keep the count small. (for example, orders → order_items. A typical shopping cart has a few dozen items). CASCADE is safe here because deletion affects a bounded number of children.

Scale-explosion risks

These are the relationships that cause trouble. The child side is unbounded. A single parent row can accumulate millions of children over time:

• The global “catch-all” anchor: Linking a massive stream of logging, audit, or event data directly to a single tenant or user. (for example, a high-activity corporate account anchoring billions of rows in an audit_logs table). In Aurora DSQL, a CASCADE delete on this parent would exceed the transaction row limit and fail.
• Viral engagement features: User activity linked directly to a single piece of content. A single viral post can accumulate millions of rows in post_likes, and a CASCADE delete on it would exceed the transaction row limit. (for example, a single popular row in posts suddenly anchoring 10,000,000 rows in post_likes). Every child insert triggers an FK validation read against that single parent row. At high volume, this becomes a measurable cost.
• High-volume product sales: In ecommerce, the products → order_items side is highly explosive. A single popular product (or a universal “Shipping Fee” item) can anchor millions of historical order rows. A CASCADE delete would attempt to remove every historical order line for that product, and fail.

Aurora DSQL-specific impact: In Aurora DSQL, unbounded FK relationships incur extra read costs on every child insert and parent row update for FK validation, and OCC conflicts if the parent’s key columns change while children are being inserted concurrently.

Architectural safeguards for Aurora DSQL

When a relationship might grow unbounded, apply these safeguards:

1. Never use CASCADE on unbounded keys. Avoid ON DELETE CASCADE on high-volume foreign keys (like product_id or tenant_id). In Aurora DSQL, deleting a parent with thousands of children exceeds the transaction row limit and fails. Use soft-deletes (is_active = false) instead, and clean up children in batched background transactions.

-- 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. Snapshot mutable attributes at write time. For transaction-based data like orders, copy attributes that change over time (such as price and product_name) directly into the child table when the transaction runs. This preserves what the customer actually paid and what the product was called at order time, without relying on the parent table’s current state. Stable identifiers like sku that never change belong only in the parent table. The FK reference is sufficient.

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. Enforce pagination and time-bounds on queries. Never allow open-ended queries like SELECT * FROM order_items WHERE product_id = X. Force strict time-bounded or cursor-based filters. Using a consistent data type on both sides of a timestamp comparison helps the query planner use indexes effectively:

-- 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. Use CHECK constraints for fixed enumerations instead of FK lookups. Low-cardinality tables (status codes, types, categories) add FK validation overhead on every insert with no real benefit. The values never change:

-- 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'))
);

Practical examples

The preceding sections cover syntax and behavior. These scenarios show how the pieces fit together in production.

Ecommerce order pipeline

Schema with scale-aware FK design:

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
);

This schema applies the safeguards from “Designing for scale” earlier: CASCADE only on the bounded relationship (orders → order_lines), RESTRICT on the unbounded one (products → order_lines), and snapshotted product data at order time.

Placing an order with deferred constraints:

The fk_order constraint is DEFERRABLE INITIALLY DEFERRED, so we can insert order lines before the parent order row. We also snapshot the product name and price at order time:

-- 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

Data migration with Aurora DSQL Loader

If you’re migrating data from another database, the Aurora DSQL Loader is the recommended approach. It’s an open source CLI that handles parallelization, connection pooling, conflict and retry handling, and AWS Identity and Access Management (IAM) authentication. Aurora DSQL also caps a write transaction at 10 MiB of modified data and 5 minutes of runtime. The Loader batches within all three limits automatically.

Step 1: Export data from the source database:

-- 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);

Step 2: Load parent tables first, then child tables:

# 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

Alternative: Load without FKs, then add constraints after:

To reduce load time, skip FK constraints during loading and add them afterward:

-- 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>');

This two-phase approach avoids FK overhead during ingestion, so the initial load runs faster, and referential integrity is validated before you go live.

Application retry logic for OCC conflicts

Because Aurora DSQL uses OCC, conflicting transactions fail at commit time with SQLSTATE 40001 rather than waiting on locks. Your application needs to handle this. Aurora DSQL distinguishes two conflict types:

• OC000 (Data conflict): Two transactions modified the same row. Retry the entire transaction.
• OC001 (Schema conflict): The session’s cached schema is stale (for example, after a concurrent DDL). Retry typically succeeds after one attempt as the session refreshes its catalog cache.

Key principles for Aurora DSQL retry logic:

• Always retry the entire transaction: After a 40001 error, the transaction is rolled back. Re-execute from BEGIN.
• Use exponential backoff with jitter: Adding randomness to the wait between retries spreads them out over time, reducing the chance that the same transactions collide again on the next attempt.
• Know which errors to retry: If you get SQLSTATE 40001, that’s an OCC conflict, so retry the transaction. Schema conflicts (OC001) typically clear up on the first retry as the session picks up the latest catalog. But if you see a foreign key violation (23503), that’s a real data problem, not a transient conflict. Retrying won’t fix it. Check your application logic instead.
• Cap retries and monitor the rate: Set a retry limit appropriate for your workload. If a transaction still fails after that, the issue is likely a hot parent row under too much concurrent write pressure, not a transient conflict. Track your 40001 error rate in production. A sustained increase points to a schema that needs restructuring (see “Designing for scale” earlier).

Clean up

To remove the resources created in this post, run:

-- 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;

Conclusion

With foreign key constraints in Aurora DSQL, you can enforce referential integrity at the database level rather than in application code. Because Aurora DSQL uses OCC instead of row-level locking, concurrent transactions proceed without blocking each other, conflicts are caught at commit time, and non-key column updates never interfere with foreign key checks. With deferred enforcement, asynchronous constraint validation, and the scale-design patterns covered in this post, you can adopt foreign keys confidently in high-throughput, distributed workloads. To learn more, see the Concurrency control page in the Aurora DSQL User Guide.

About the authors