Working with foreign key constraints in Aurora DSQL

With foreign key constraints in Aurora DSQL, you can push an application's referential integrity logic into the database. Aurora DSQL supports the NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT referential actions. It also supports the MATCH FULL and MATCH SIMPLE match types, and deferrable foreign key constraints. For the full syntax breakdown, see Foreign key constraints.

How Aurora DSQL maintains referential integrity

Aurora DSQL maintains referential integrity in two steps: snapshot verification as the transaction runs, and conflict resolution at commit time. Together, these steps guarantee that a committed transaction never violates a foreign key constraint.

Snapshot verification. Every transaction in Aurora DSQL runs against a consistent snapshot of the database taken at its start time. When you insert or update a referencing row, Aurora DSQL reads the referenced table at your transaction's start-time snapshot to confirm the referenced key exists. When you delete or update a referenced key, Aurora DSQL reads the referencing table in the transaction snapshot. It confirms that no referencing rows exist (for RESTRICT) or that the operation leaves no orphaned rows (for NO ACTION). Because this verification reads from the transaction start-time snapshot instead of taking a lock, other transactions can continue to modify the referenced and referencing tables in parallel.

Commit-time resolution. Snapshot verification ensures that the constraint held at the transaction's start time, but not between start and commit. A concurrent transaction can delete the referenced row or insert a conflicting referencing row after your transaction started. To adjudicate these conflicts, Aurora DSQL implicitly applies the KEY SHARE clause to referenced rows to detect whether any concurrent change invalidated your snapshot. If Aurora DSQL detects a conflict, it fails the transaction with a serialization error. For more information about how the KEY SHARE clause affects concurrent transactions, see Concurrency control in Aurora DSQL.

Referential integrity checks incur extra reads

All data manipulation language (DML) operations on referenced or referencing tables incur extra reads to guarantee referential integrity. Before you add a foreign key constraint to a table, benchmark the workload and validate that the performance characteristics meet your expectations.

Example scenarios

In the following scenario, the orders table has a foreign key constraint on its product_id column that references the products table. This makes products the referenced table and orders the referencing table.

CREATE TABLE products (
    product_id integer PRIMARY KEY,
    name text,
    price numeric
);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_id integer REFERENCES products,
    quantity integer
);
INSERT INTO products VALUES (1, 'Widget', 9.99);
    

Conflict: concurrent delete and insert

In this scenario, one session deletes a referenced row while another session inserts a referencing row.

-- Session A
BEGIN;
DELETE FROM products WHERE product_id = 1;

-- Session B
BEGIN;
INSERT INTO orders VALUES (100, 1, 5);

-- Session A
COMMIT;  -- succeeds

-- Session B
COMMIT;  -- fails with serialization error
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.

No conflict: non-key column update

In this scenario, one session updates a non-key column on the referenced row while another session inserts a referencing row.

-- Session A
BEGIN;
UPDATE products SET name = 'Super Widget' WHERE product_id = 1;

-- Session B
BEGIN;
INSERT INTO orders VALUES (101, 1, 3);

-- Session A
COMMIT;  -- succeeds

-- Session B
COMMIT;  -- succeeds

Updating name (a non-key column) doesn't conflict with the foreign key on product_id. The referencing row only cares that the referenced row's key columns stay the same.

Best practices with foreign keys in Aurora DSQL

Implement retry logic

Conflicts cause errors instead of waits. Design your workload to retry failed transactions. For more information about concurrency in Aurora DSQL, see Concurrency control in Aurora DSQL.

Minimize key-column churn on heavily-referenced rows

If multiple referencing rows reference the same row and its key columns change often, consider restructuring the schema. Move frequently-changing values to non-key columns so that the referenced column stays stable. Changing non-key columns on the referenced table doesn't conflict with referencing inserts.