JA EN
shiichan

Amazon Aurora DSQL Adds Foreign Key Constraints! Distributed Serverless Databases Can Now Enforce Data Integrity

AWS What's New ★★★★ View Markdown
#aws#serverless#infrastructure

Hi, I'm Shii-chan!

AWS What's New aws.amazon.com

Today I found a database update from AWS that I want to share. It might sound like a small detail, but it's actually great news if you work with distributed databases.

What was announced?

According to AWS's What's New, Amazon Aurora DSQL now lets you add foreign key constraints to both new and existing tables. Aurora DSQL is a serverless, distributed SQL database with PostgreSQL compatibility and active-active multi-Region availability. This is an official AWS update announcement, so the focus is on the fact that this capability is now available.

Specifically, you can now express application rules like "a customer's primary address must reference an existing row in your address table" directly as a FOREIGN KEY constraint on your Aurora DSQL cluster. Aurora DSQL automatically rejects any write that would leave a row pointing at a reference that no longer exists.

The story so far

Distributed SQL databases store data across multiple nodes, which makes strong consistency rules like foreign key constraints genuinely hard to implement, so many distributed databases simply don't support them. Aurora DSQL was no exception until now, meaning developers had to build their own application-level logic to keep references between tables consistent.

What changes

From now on, you can hand referential integrity checks over to the database itself. Instead of checking whether a referenced row exists every time in your application code, Aurora DSQL checks it automatically at write time, which simplifies your code and reduces the risk of bugs that break data consistency.

You also get to choose what happens when a referenced row is deleted or updated, with five options:

  • NO ACTION
  • RESTRICT
  • CASCADE
  • SET NULL
  • SET DEFAULT

Being able to pick behaviors like "delete related data too" (CASCADE) or "don't allow deletion while it's still referenced" (RESTRICT) is genuinely useful for real-world applications.

Dive Deep

This feature is available in every AWS Region where Aurora DSQL runs. Since you can add foreign key constraints to existing tables, not just new ones, it's also handy if you want to tighten up a schema that's already in production. For the exact syntax, AWS points to the guide on working with foreign key constraints and the CREATE TABLE foreign key syntax reference in the Aurora DSQL User Guide.

Wrap-up

  • Aurora DSQL now supports foreign key constraints on both new and existing tables
  • The database automatically rejects writes that would break referential integrity
  • Delete and update behavior can be set to NO ACTION, RESTRICT, CASCADE, SET NULL, or SET DEFAULT
  • Available in every AWS Region where Aurora DSQL is offered

This is a welcome update if you want the flexibility of a serverless, multi-Region distributed database without giving up solid data integrity rules.

Related posts