Working with foreign key constraints in Aurora DSQL

This article explains how to work with foreign key constraints in Amazon Aurora DSQL, which uses optimistic concurrency control instead of row-level locking to enforce referential integrity in distributed databases.

• Define foreign keys using PostgreSQL syntax with support for all referential actions and deferrable constraints
• Add constraints to existing tables asynchronously without downtime using NOT VALID and ALTER TABLE ASYNC VALIDATE CONSTRAINT
• Use immediate enforcement (default) for snapshot-based validation at statement time, or deferred enforcement to validate at commit time
• Leverage OCC conflict detection: no row-level blocking, conflicts resolved at commit with SQLSTATE 40001 errors
• Non-key column updates don't conflict with concurrent FK checks; only key column changes trigger conflicts
• Avoid CASCADE on unbounded relationships; use RESTRICT with soft-deletes to prevent exceeding transaction row limits
• Snapshot mutable attributes (price, name) in child tables at write time to preserve historical accuracy
• Implement application retry logic with exponential backoff for OCC conflicts (SQLSTATE 40001)
• Use Aurora DSQL Loader for data migration, loading parent tables first, then adding constraints asynchronously

Aurora DSQL enables database-level referential integrity with high concurrency and throughput by eliminating locks and validating constraints against snapshots rather than blocking on row-level locks.