Aurora DSQL Now Supports Foreign Key Constraints, So I Tried Checking Its 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.

You can now shift referential integrity checks from the application to the database. In this article, I used psql and Python to check what is returned when concurrent operations are performed on tables with foreign key constraints.

Verification

Test environment

The server I connected to was PostgreSQL 16 compatible.

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

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

The environment used for the verification is as follows.

• Region: ap-northeast-1
• Cluster configuration: single-Region configuration with no linked cluster
• psql: 17 series
• 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 the following verification is these two 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 is effective for both inserting a child row and deleting a parent row. First, this is the result of running INSERT INTO employees (id, dept_id, name) VALUES (2, 999, 'Bob'); specifying a parent row that does not exist.

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, this is the result of trying to delete a parent row that is referenced by a child row, using 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 error text included the name of the violated constraint and the key value.

When adding a constraint to an existing table afterwards, Aurora DSQL accepted only statements with NOT VALID. This is a specification stated explicitly in the ALTER TABLE documentation. For this check, I started from a state where one row had been inserted into each of 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 primary key is displayed as PRIMARY KEY (id) INCLUDE (pid) because Aurora DSQL creates an index that includes all columns of the table when a primary key is defined (Primary keys in Aurora DSQL). I did not write INCLUDE when creating the table.

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

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

The job_id in the following output is a value that changes with each execution, so it is masked.

           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 completed, convalidated became t, and NOT VALID was removed from the definition. Note that this series of steps for confirming the addition to an existing table was carried out on a different cluster (with the same configuration) from the other sections.

Conflicts in concurrent transactions

I performed updates involving the foreign key constraint from two sessions at the same time. To prepare a parent row with no child rows, I added a row with id = 2 to departments beforehand. The steps are as follows.

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

I ran it once, and Session A, which committed later, failed with 40001. This is Session A's output. The start and end times are output by clock_timestamp() in this script, and the error line is for the immediately preceding COMMIT.

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

BEGIN;
BEGIN
-- 子行が1件も無い親行を削除する（この時点では FK 違反にならない）
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)

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

Here are the server-side recorded times (UTC). A's end time was measured immediately after the failed commit.

Even while Session A had not committed its DELETE, it took about 0.11 seconds from Session B's INSERT to the completion of its commit, and no waiting due to locks occurred. Session A's commit failed about 5.08 seconds after it started, right after the 5-second wait ended. The SQLSTATE was 40001, a different code from the constraint violation 23503. The foreign key constraint name did not appear in the error text; it was a conflict message, change conflicts with another transaction (OC000).

This behavior is described 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 steps on tables of the same shape without a foreign key constraint, the commits of both sessions succeeded this time, and 40001 did not occur.

I checked how this looks from the application side using Python. The following script opens two connections with psycopg and reproduces the same steps. It determines and prints the SQLSTATE of the exception on the side that commits later. For the connection, I used an authentication token passed through the PGPASSWORD environment variable. The token was issued with aws dsql generate-db-connect-admin-auth-token.

#!/usr/bin/env python3
"""Aurora DSQL: 外部キー制約付きテーブルの並行更新で返る SQLSTATE を確認する。

セッションA が子行を持たない親行を削除し、コミットする前にセッションB が
同じ親行を参照する子行を追加してコミットする。後からコミットするセッションA で
何が返るかを、例外の SQLSTATE で判定して出力する（リトライは行わない）。
"""
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:
    """検証対象の親行（子行なし）を用意する。"""
    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:
        # セッションA: 子行が1件も無い親行を削除する（この時点ではエラーにならない）
        conn_a.execute("DELETE FROM departments WHERE id = %s", (PARENT_ID,))
        print("session A: DELETE departments -> ok (未コミット)")

        # セッションB: 同じ親行を参照する子行を追加して先にコミットする
        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")

        # セッションA: 後からコミットする側で何が返るかを確認する
        try:
            conn_a.commit()
            print("session A: COMMIT -> ok（競合なし）")
        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（並行トランザクションとの競合）")
            elif exc.sqlstate == "23503":
                print("  -> foreign_key_violation（外部キー制約違反）")
            else:
                print("  -> 上記以外のエラー")
    finally:
        conn_a.close()
        conn_b.close()

if __name__ == "__main__":
    main()

Here are the execution results.

session A: DELETE departments -> ok (未コミット)
session B: INSERT employees -> committed
session A: COMMIT -> SerializationFailure
  sqlstate: 40001
  message : change conflicts with another transaction (OC000)
  -> serialization_failure（並行トランザクションとの競合）

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

As a way of dealing with this conflict, the official documentation lists retrying the failed transaction as a best practice.

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

Summary

With Aurora DSQL now supporting foreign key constraints, it has become easier to guarantee referential integrity on the database side. However, the behavior when a conflict occurs is not the same as with PostgreSQL foreign key constraints. Instead of being made to wait on locks, the transaction fails.

If you run a workload that relies on foreign key constraints on Aurora DSQL, I recommend thoroughly evaluating this difference and the error handling on the application side.