The Aurora DSQL constraints reference

DSQL speaks the PostgreSQL wire protocol, so your driver connects and your first SELECT works. The refusals start when you write a schema. This is the list, what to write instead, and the handful of consequences that escape the schema file and end up in your application code.

Most of what this page said in August 2026 is no longer true, and that is the most useful thing on it. Foreign keys, ON CONFLICT, sequences, JSON, DROP COLUMN and views all arrived between February and September. Everything below was re-run against a production cluster on October 18, 2026, one statement at a time, and the rows say what came back.

On this page: the schema table, the transaction limits, foreign keys arrived with a bill, consequence 1: unique indexes do not enforce while they build, consequence 2: NOT NULL is decided once, consequence 3: every write is a retry loop, what this rules out, what changed in 2026, verify it yourself.

The schema table

Each row is one statement sent to a live cluster. Where DSQL refused it, the error text is quoted, because that is what you will be searching for.

Feature	As of October 18, 2026	What to write instead
FOREIGN KEY, REFERENCES	Supported since August 26, 2026	Use them, with the costs in the section below
INSERT ... ON CONFLICT	Supported. DO NOTHING and DO UPDATE both verified	The PostgreSQL upsert, unchanged
serial	Refused: type "serial" does not exist	bigint GENERATED ALWAYS AS IDENTITY (CACHE 1), or a uuid
Sequences and identity columns	Supported since February 13, 2026, with rules	CACHE is mandatory and must be 1 or at least 65536. Identity columns must be bigint
json, jsonb	Supported since May and June 2026	Every operator and function from PostgreSQL 9.16. Not indexable: datatype jsonb is not supported in a key
Array columns such as text[]	Refused: datatype text[] not supported	jsonb. Arrays exist only inside a query
CREATE INDEX	Refused: unsupported mode. please use CREATE INDEX ASYNC.	CREATE INDEX ASYNC, which returns a job id instead of blocking
DESC in an index key	Refused: specifying sort order not supported for index keys	Nothing. The planner scans the index backwards
Expression, partial and INCLUDE indexes	Supported since August and September 2026	ON t ((lower(email))), WHERE deleted_at IS NULL
ALTER TABLE ADD COLUMN with NOT NULL or DEFAULT	Refused: ALTER TABLE ADD COLUMN with constraint not supported	Add it nullable, then ALTER COLUMN ... SET DEFAULT. There is no SET NOT NULL. See consequence 2
ALTER TABLE DROP COLUMN	Supported since August 3, 2026	Not for primary key columns. A dropped column still counts toward the 1,600-column ceiling
CHECK	In CREATE TABLE, yes. Afterwards, ADD CONSTRAINT ... NOT VALID only	Then ALTER TABLE ASYNC ... VALIDATE CONSTRAINT, which runs as a job in sys.jobs
Views	Supported	Not materialized: unsupported statement: CreateTableAs
Functions	LANGUAGE sql only	PL/pgSQL is refused: CREATE FUNCTION with language plpgsql not supported
Triggers	Refused: CREATE TRIGGER not supported	Application code, or a change data capture stream into Kinesis
CREATE EXTENSION	Refused: unsupported statement: CreateExtension	Nothing. No pg_trgm, no citext, no PostGIS, no pgvector
TRUNCATE	Refused: unsupported statement: Truncate	DELETE FROM in 3,000-row chunks, or DROP TABLE and CREATE TABLE
Temporary tables	Refused: TEMPORARY or TEMP table not supported for CREATE TABLE	A CTE, or a real table you clean up yourself
SELECT ... FOR UPDATE	Supported with any predicate since August 24, 2026	It is conflict detection at commit, not a lock. See consequence 3
FOR SHARE, FOR NO KEY UPDATE	Refused: locking clauses other than FOR UPDATE/FOR KEY SHARE are not supported	FOR KEY SHARE, added August 25, 2026
SERIALIZABLE	Refused: Unsupported isolation level: SERIALIZABLE	Nothing. Every transaction is REPEATABLE READ
Collation	C only	An expression index on lower(col) for case-insensitive lookups
Databases	One per cluster, named postgres	Schemas, at most ten
System catalog	Partial	DataGrip and similar tools show less schema than they expect to

The extension row is still the one that surprises people most, because it is not one missing feature. It is the entire PostgreSQL ecosystem. pgvector in particular: Aurora PostgreSQL has it, Aurora DSQL does not, and the two products being one word apart causes a great deal of confusion. If you need semantic search, it lives outside DSQL.

The transaction limits

AWS's documented limits as of October 2026. The row limit was confirmed by hand: a single INSERT ... SELECT of 2,999 rows commits, and 3,001 fails with 54000, transaction row limit exceeded.

Limit	Value
Rows modified per transaction	3,000
Data modified per transaction	10 MiB
Transaction duration	5 minutes
DDL statements per transaction	1, and never in the same transaction as DML
Connection duration	60 minutes
Memory per query	128 MiB
Columns per table	255, or 1,600 counting dropped ones
Columns per index, and bytes per key	8, and 1 KiB
Indexes per table	24
Tables per database	1,000

The row limit is the one that quietly rules out whole categories of application. A nightly job that rewrites a 200,000-row table is not a large job by any normal standard, and in DSQL it is 67 transactions that you have to chunk, checkpoint, and make resumable yourself. If your workload has bulk in it, price that work in before you commit to the database. Foreign key cascades count toward the same limit: an ON DELETE CASCADE that would remove 5,000 child rows fails the delete.

The DDL rule catches migration tools. BEGIN; CREATE TABLE a; CREATE TABLE b; COMMIT; is refused with multiple ddl statements not supported in a transaction, and CREATE TABLE followed by an INSERT into it is refused with ddl and dml are not supported in the same transaction. A tool that wraps a migration file in one transaction does not work here. Each statement stands alone.

The one-hour connection cap interacts with IAM tokens, which expire sooner than that. That is its own chapter.

Foreign keys arrived with a bill

Foreign keys work, and they work the way the manual says: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT, MATCH FULL and MATCH SIMPLE, deferrable or not. Inserting a child row whose parent does not exist is refused with 23503, and so is deleting a parent that still has children. Verified.

What the manual says more quietly is how they are enforced. There are no locks in DSQL, so a foreign key is checked against the snapshot your transaction started with, and then checked again at commit by treating the parent row as if you had run SELECT ... FOR KEY SHARE on it. If another transaction deleted that parent between your start and your commit, yours fails with 40001. In other words, foreign keys route through the same retry loop as everything else, and every insert into a child table now costs an extra read of the parent.

Two practical rules follow. Prefer NO ACTION or RESTRICT where the number of children is unbounded, because a cascade is a write and writes are capped at 3,000 rows. And add constraints to existing tables with ALTER TABLE ... ADD CONSTRAINT ... NOT VALID, which is the only form accepted, then ALTER TABLE ASYNC ... VALIDATE CONSTRAINT and watch sys.jobs; the constraint applies to new rows immediately and to old rows once the job finishes.

Consequence 1: unique indexes do not enforce while they build

CREATE UNIQUE INDEX ASYNC returns a job id immediately and the index builds in the background. On a table with one row, the build took 27 seconds on October 18, 2026. On a real table it takes longer. Until the job finishes, the index is marked INVALID and nothing stops a duplicate going in; with the job still submitted, a second row with the same email inserted without complaint.

What happens next is not what the first version of this page said. The build validates the whole table before it finishes. If it finds duplicates, the job fails with found duplicate key(s) while validating index uniqueness, the index stays INVALID, and from that moment it enforces uniqueness on new rows anyway: inserting another duplicate after the failed build is refused with 23505. So you do not end up with a unique index over non-unique data. You end up with a failed job, an index you have to drop, rows you have to dedupe by hand, and a second build, while the application keeps running against a table that is not actually unique. That is still a bad afternoon, and the window is still open for as long as the job runs.

Three defenses, and you want all of them.

Prefer a PRIMARY KEY where you can. A primary key is enforced from the first insert, with no build window at all. If a natural or composite key exists, use it rather than a surrogate id plus a unique index:

-- Enforced immediately. There is no window.
CREATE TABLE short_ids (
  workspace_id uuid NOT NULL,
  short        text NOT NULL,
  thread_id    uuid NOT NULL,
  PRIMARY KEY (workspace_id, short)
);

Create the remaining unique indexes on an empty table. Before there is traffic, the build window is harmless. This is a strong argument for getting your indexes into schema.sql rather than discovering you need one at month four, when the safe way to add it involves a quiet period you did not think this database required.

Wait for the job in your migration. CALL sys.wait_for_job('<job id>') blocks until the build completes or fails and returns whether it succeeded. SELECT status, details FROM sys.jobs WHERE job_id = '<job id>' says why. A migration that moves on before the job finishes has not finished.

Consequence 2: NOT NULL is decided once

ALTER TABLE ADD COLUMN takes a name and a type and nothing else. NOT NULL is refused, DEFAULT is refused, and there is no ALTER COLUMN ... SET NOT NULL in the grammar at all. A column added after the table exists is nullable for the life of the table.

DROP COLUMN exists now, so a mistaken column can be removed. It cannot be fixed in place. The only route from nullable to NOT NULL is a new column, a backfill in 3,000-row chunks, and the application reading the new one. SET DEFAULT after the fact does work, and applies to rows inserted from then on, which covers the common case of a flag that should default to false.

This inverts the usual migration instinct. In PostgreSQL, adding a column speculatively is close to free because you can tighten it later. In DSQL, every column's nullability is a permanent decision made under whatever amount of thought you gave it that afternoon. The practical consequence is that schema.sql deserves more design time than you are used to giving it, and that forward-only migration files are the only migration story here. There is no down migration, because down is not a direction this database moves.

Consequence 3: every write is a retry loop

DSQL is optimistic. Two transactions that touch the same row both run to COMMIT, and the one that arrives second fails with SQLSTATE 40001, change conflicts with another transaction (OC000). That is not an error path. It is the normal behaviour of the database under any concurrency at all.

The same code arrives for a second reason as OC001, schema has been updated by another transaction. Any DDL in any session bumps the catalog version, and the next statement from a session holding the old version fails once and succeeds on retry. A session that sat idle through someone else's migration sees it too, the first time it does anything.

So every write goes through a wrapper like this one, which is lifted from the code serving this page:

export const isSerializationFailure = (err: any): boolean => err?.code === '40001';

export const withRetry = async <T>(fn: () => Promise<T>, attempts = 3): Promise<T> => {
  let lastError: any;
  for (let i = 0; i < attempts; i += 1) {
    try {
      return await fn();
    } catch (err) {
      if (!isSerializationFailure(err)) throw err;
      lastError = err;
      // Brief, jittered. Two writers retrying in lockstep just collide again.
      await new Promise((r) => setTimeout(r, 25 * (i + 1) + Math.random() * 25));
    }
  }
  throw lastError;
};

Reads never need it. Writes always do, and the function inside has to be safe to run twice, because it will be. That is the subject of its own chapter. The point here is that it is not optional and it is not an edge case.

What this rules out

Read as a whole, the table now says something different from what it said in August.

Migrating a plain PostgreSQL schema is a port. Foreign keys, sequences, upserts, views, CHECK constraints and JSON columns survive the move. serial becomes an identity column with a cache size, every CREATE INDEX gains ASYNC, the migration runner learns to send one statement per transaction, and every write grows a retry. That is a list, and lists get finished.

Migrating an application that lives in the database is still a rewrite. Triggers, PL/pgSQL and extensions are not on the roadmap anyone has published. If your integrity rules are triggers, your business logic is stored procedures, or your search is pg_trgm or pgvector, that logic has to move into application code that does not exist yet, and you will find that out in week three.

Anything with real bulk in it needs a second system. Analytics rollups, imports, backfills, nightly recomputation. All possible in 3,000-row chunks inside five-minute transactions; none pleasant.

Semantic search lives elsewhere. No pgvector, no vector type, no extension mechanism to add one. This is worth knowing early, because it is now a normal expectation for a new product, and the answer involves a second data store.

None of this makes DSQL a bad database. It makes it a specific one, aimed at applications written from scratch that want relational modeling and global consistency without operating anything. If that is the application you are writing, the constraints are mostly a schema-design exercise you do once. If it is not, you want to find that out now rather than in week three.

What changed in 2026

The dated list, from the release notes, of the changes that moved rows in the table above.

Date	Change
February 13	Sequences and identity columns
February 25	Playground, DBeaver plugin, VS Code SQLTools driver, Prisma, Flyway and Tortoise support
May 4 and June 8	json, then jsonb, both compressed
July 6	SET DEFAULT, DROP DEFAULT, DROP NOT NULL, ADD GENERATED AS IDENTITY, DROP CONSTRAINT
July 8	Change data capture to Kinesis, generally available
August 3	DROP COLUMN; ADD CONSTRAINT ... NOT VALID and async VALIDATE CONSTRAINT
August 13	Indexes on expressions
August 24 and 25	FOR UPDATE with any predicate; FOR KEY SHARE; numeric up to precision 1000
August 26	Foreign key constraints; compression for text and varchar
August 27	Extended statistics
September 15	Partial indexes

This page was first published on August 15, 2026 and rewritten on October 18, 2026. It will be wrong again. When you find the row, say so in the comments and it gets re-verified.

Verify it yourself

Nothing above needs to be taken on trust. A single-region cluster creates in seconds, the free tier covers 100,000 DPUs a month, and psql connects with a token from aws dsql generate-db-connect-admin-auth-token. Send each statement from the table on its own, read the error, and compare. The method behind this page is exactly that, run from a script that creates tables with a throwaway prefix and drops them afterwards, so that the cluster serving this page is the one the page was checked against.