Adds support for deferrable foreign key constraints in PostgreSQL by benedikt ·...

benedikt commented on Feb 18 •

Summary

By default, foreign key constraints in PostgreSQL are checked after each statement. This works for most use cases, but becomes a major limitation when creating related records before the parent record is inserted into the database.

One example of this is looking up / creating a person via one or more unique alias.

Person.transaction do
  alias = Alias
    .create_with(user_id: SecureRandom.uuid)
    .create_or_find_by(name: "DHH")

  person = Person
    .create_with(name: "David Heinemeier Hansson")
    .create_or_find_by(id: alias.user_id)
end

Using the default behavior, the transaction would fail when executing the first INSERT statement.

This pull request adds support for deferrable foreign key constraints by adding a new option to the add_foreign_key statement in migrations:

add_foreign_key :aliases, :person, deferrable: true

The deferrable: true leaves the default behavior, but allows manually deferring the checks using SET CONSTRAINTS ALL DEFERRED within a transaction. This will cause the foreign keys to be checked after the transaction.

It's also possible to adjust the default behavior from an immediate check (after the statement), to a deferred check (after the transaction).

add_foreign_key :aliases, :person, deferrable: :deferred

Other Information

There is a pull request #17094 from 2014 that implements generic SQL options on add_foreign_key. Pull request #40192 recently implemented the validate aspect of that.