Aurora DSQL added support for foreign key constraints, so I tried to verify the behavior during conflicts

Introduction

On August 27, 2026, Amazon Aurora DSQL added support for foreign key constraints.

https://aws.amazon.com/about-aws/whats-new/2026/08/aurora-dsql-foreign-key-constraints/

This applies not only to newly created tables.

Amazon Aurora DSQL now lets you add foreign key constraints to new and existing tables.

Referential integrity checks can now be delegated from the application to the database. In this article, I verified what is returned when tables with foreign key constraints are operated on concurrently, both from psql and Python.

Verification Details

Verification Environment

The server connected to was PostgreSQL 16 compatible.

    version
---------------
 PostgreSQL 16
(1 row)

 server_version
----------------
 16.15
(1 row)

The environment used for verification is as follows.

• Region: ap-northeast-1
• Cluster configuration: Single-region configuration with no linked clusters
• psql: version 17
• Python: 3.12
• psycopg: 3.2.9

Defining Foreign Key Constraints

I created a departments table and an employees table that references it.

CREATE TABLE departments (
    id   int PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE employees (
    id      int PRIMARY KEY,
    dept_id int NOT NULL REFERENCES departments(id),
    name    text NOT NULL
);

Both succeeded. The initial data used in subsequent verifications consists of the following 2 rows.

INSERT INTO departments (id, name) VALUES (1, 'Sales');
INSERT INTO employees (id, dept_id, name) VALUES (1, 1, 'Alice');

I confirmed that the constraint was in effect for both a child insertion and a parent deletion. First, here is the result of executing INSERT INTO employees (id, dept_id, name) VALUES (2, 999, 'Bob'); specifying a non-existent parent row.

psql:/work/sql/02-fk-basic.sql:22: ERROR:  23503: insert or update on table "employees" violates foreign key constraint "employees_dept_id_fkey"
DETAIL:  Key (dept_id)=(999) is not present in table "departments".
SCHEMA NAME:  public
TABLE NAME:  employees
CONSTRAINT NAME:  employees_dept_id_fkey

Next, here is the result of attempting to delete a parent row referenced by a child row with DELETE FROM departments WHERE id = 1;.

psql:/work/sql/02-fk-basic.sql:25: ERROR:  23503: update or delete on table "departments" violates foreign key constraint "employees_dept_id_fkey" on table "employees"
DETAIL:  Key (id)=(1) is still referenced from table "employees".
SCHEMA NAME:  public
TABLE NAME:  employees
CONSTRAINT NAME:  employees_dept_id_fkey

In both cases, the SQLSTATE was 23503, and the violated constraint name and key value were included in the error message.

When adding a constraint to an existing table after the fact, Aurora DSQL only accepts statements with NOT VALID appended. This is a specification explicitly stated in the ALTER TABLE documentation. For this verification, I started with a state where one row each had been inserted into the following two tables.

CREATE TABLE p_alt (id int PRIMARY KEY);
CREATE TABLE c_alt (id int PRIMARY KEY, pid int);
INSERT INTO p_alt VALUES (1);
INSERT INTO c_alt VALUES (1, 1);

First, let's try adding the constraint without NOT VALID.

ALTER TABLE c_alt ADD CONSTRAINT c_alt_pid_fkey FOREIGN KEY (pid) REFERENCES p_alt(id);

Aurora DSQL did not accept this statement as-is.

psql:/work/sql/12-alter-add-constraint-notvalid.sql:11: ERROR:  0A000: unsupported ALTER TABLE ADD CONSTRAINT statement

The statement with NOT VALID appended at the end succeeded.

ALTER TABLE c_alt ADD CONSTRAINT c_alt_pid_fkey FOREIGN KEY (pid) REFERENCES p_alt(id) NOT VALID;

The constraint is registered in an unvalidated state with convalidated set to f.

    conname     | convalidated |               pg_get_constraintdef
----------------+--------------+--------------------------------------------------
 c_alt_pid_fkey | f            | FOREIGN KEY (pid) REFERENCES p_alt(id) NOT VALID
 c_alt_pkey     | t            | PRIMARY KEY (id) INCLUDE (pid)
(2 rows)

The reason the primary key is displayed as PRIMARY KEY (id) INCLUDE (pid) is that Aurora DSQL creates an index including all columns of the table when defining the primary key (see Primary keys in Aurora DSQL). This does not mean INCLUDE was written at table creation time.

Even in the unvalidated state, the constraint applies to newly inserted rows. INSERT INTO c_alt VALUES (2, 999); was rejected with 23503.

Validation of existing rows is performed with ALTER TABLE ASYNC c_alt VALIDATE CONSTRAINT c_alt_pid_fkey;. This statement immediately returns a job_id. The validation ran as a VALIDATE_CONSTRAINT job on sys.jobs.

The job_id in the following output is masked because it changes with each execution.

           job_id           |  status   |      job_type
----------------------------+-----------+---------------------
 xxxxxxxxxxxxxxxxxxxxxxxxxx | completed | VALIDATE_CONSTRAINT
(1 row)

    conname     | convalidated |          pg_get_constraintdef
----------------+--------------+----------------------------------------
 c_alt_pid_fkey | t            | FOREIGN KEY (pid) REFERENCES p_alt(id)
 c_alt_pkey     | t            | PRIMARY KEY (id) INCLUDE (pid)
(2 rows)

After the job completes, convalidated becomes t and NOT VALID is removed from the definition. Note that this series of steps confirming addition to an existing table was performed on a different cluster (with the same configuration) from the other sections.

Conflicts Between Concurrent Transactions

I performed updates involving foreign key constraints from two sessions simultaneously. To prepare a parent row with no child rows, a row with id = 2 was added to departments in advance. The procedure is as follows.

1. Session A deletes the parent row with id = 2 and waits 5 seconds without committing
2. 2 seconds after Session A starts, Session B adds a child row referencing the same parent row and commits first
3. Session A commits

The test was run once, and Session A, which committed later, failed with 40001. Here is the output from Session A. The start and end times are output by clock_timestamp() in this script, and the error line is for the preceding COMMIT.

        session_a_start
-------------------------------
 2026-08-29 06:05:59.926605+00
(1 row)

BEGIN;
BEGIN
-- Delete the parent row with no child rows (no FK violation at this point)
DELETE FROM departments WHERE id = 2;
DELETE 1
SELECT pg_sleep(5);
 pg_sleep
----------

(1 row)

COMMIT;
SELECT clock_timestamp() AS session_a_end;
psql:/work/sql/03a-session-a.sql:8: ERROR:  40001: change conflicts with another transaction (OC000)
         session_a_end
-------------------------------
 2026-08-29 06:06:05.003089+00
(1 row)

Session B's INSERT INTO employees (id, dept_id, name) VALUES (4, 2, 'Dave'); and commit both succeeded.

Here are the timestamps recorded on the server side (UTC) laid out together. Session A's end time was measured immediately after the failed commit.

Even while Session A's DELETE was uncommitted, Session B's entire process from INSERT to commit completion took about 0.11 seconds, with no waiting due to locks. Session A's commit failed approximately 5.08 seconds after it started — immediately after the 5-second wait ended. The SQLSTATE was 40001, a different code from the constraint violation code 23503. The foreign key constraint name did not appear in the error message; instead it was a conflict message: change conflicts with another transaction (OC000).

This behavior is documented in the official documentation.

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.

When I tried the same procedure on tables of the same shape without foreign key constraints, both sessions' commits succeeded and 40001 did not occur.

I used Python to verify how this looks from the application side. The following script opens two connections with psycopg and reproduces the same procedure. It determines the SQLSTATE of the exception on the side that commits later and outputs it. The connections use an authentication token passed via the PGPASSWORD environment variable. The token was issued with aws dsql generate-db-connect-admin-auth-token.

#!/usr/bin/env python3
"""Aurora DSQL: Verify the SQLSTATE returned on concurrent updates to a table with foreign key constraints.

Session A deletes a parent row with no child rows, and before committing, Session B
adds a child row referencing the same parent row and commits. This checks what is
returned for Session A, which commits later, by determining its exception's SQLSTATE
and printing it (no retries are performed).
"""
import os

import psycopg

CONNINFO = (
    f"host={os.environ['DSQL_ENDPOINT']} port=5432 dbname=postgres "
    f"user=admin password={os.environ['PGPASSWORD']} sslmode=require"
)

PARENT_ID = 3
CHILD_ID = 5

def setup() -> None:
    """Prepare the parent row (with no child rows) to be used in the test."""
    with psycopg.connect(CONNINFO, autocommit=True) as conn:
        conn.execute("DELETE FROM employees WHERE id = %s", (CHILD_ID,))
        conn.execute(
            "INSERT INTO departments (id, name) VALUES (%s, %s) ON CONFLICT DO NOTHING",
            (PARENT_ID, "Finance"),
        )

def main() -> None:
    setup()

    conn_a = psycopg.connect(CONNINFO)
    conn_b = psycopg.connect(CONNINFO)
    try:
        # Session A: Delete the parent row with no child rows (no error at this point)
        conn_a.execute("DELETE FROM departments WHERE id = %s", (PARENT_ID,))
        print("session A: DELETE departments -> ok (uncommitted)")

        # Session B: Add a child row referencing the same parent row and commit first
        conn_b.execute(
            "INSERT INTO employees (id, dept_id, name) VALUES (%s, %s, %s)",
            (CHILD_ID, PARENT_ID, "Erin"),
        )
        conn_b.commit()
        print("session B: INSERT employees -> committed")

        # Session A: Check what is returned on the side that commits later
        try:
            conn_a.commit()
            print("session A: COMMIT -> ok (no conflict)")
        except psycopg.Error as exc:
            print(f"session A: COMMIT -> {type(exc).__name__}")
            print(f"  sqlstate: {exc.sqlstate}")
            print(f"  message : {exc}")
            if exc.sqlstate == "40001":
                print("  -> serialization_failure (conflict with concurrent transaction)")
            elif exc.sqlstate == "23503":
                print("  -> foreign_key_violation (foreign key constraint violation)")
            else:
                print("  -> error other than the above")
    finally:
        conn_a.close()
        conn_b.close()

if __name__ == "__main__":
    main()

Here are the execution results.

session A: DELETE departments -> ok (uncommitted)
session B: INSERT employees -> committed
session A: COMMIT -> SerializationFailure
  sqlstate: 40001
  message : change conflicts with another transaction (OC000)
  -> serialization_failure (conflict with concurrent transaction)

In psycopg, it is raised as psycopg.errors.SerializationFailure, and the sqlstate and message were the same as what was seen in psql.

As a measure for handling this conflict, the official documentation lists retrying failed transactions as a best practice.

Implement retry logic
Conflicts cause errors instead of waits. Design your workload to retry failed transactions.

Summary

Aurora DSQL now supports foreign key constraints, making it easier to enforce referential integrity on the database side. However, the behavior when a conflict occurs is not the same as with PostgreSQL's foreign key constraints. Rather than being made to wait due to a lock, the transaction fails.

If you plan to run workloads that rely on foreign key constraints on Aurora DSQL, I recommend thoroughly evaluating this difference and your application's error handling.