When designing your application's data layer, you might want to enforce separation of concerns and
split tables by the data they hold, for example, moving all optional user data to a profile table and
keeping track of the most important details like their name, email address and phone number in the
primary account table.
Until now, everything described, you'll be able to realize with standard foreign keys, nothing unusual to be seen. But we'll introduce another requirement: An account must be linked to a profile and vice-versa. Of course, we could just create both and not worry about any case where one gets dropped by using cascading deletes, but we can go one step further.
🔒 Creating a deadlock relation
We'll create two foreign keys, both on the account and profile id fields which identify an account, pointing
to the id field of the reverse table. Since we've also chosen id to act as the primary key, we'll have a unique, non-nullable
configuration added implicitly which requires us to create both an account and profile with the same identifier at once.
The problem we've now pushed ourselves into, is, that we cannot create records since we'll immediately receive a foreign key constraint violation upon creating either the account or profile, since the other side just doesn't exist yet.
However, by using, what's called a deferrable constraint in a deferred state, we can mitigate this issue completely!
⌚️ Deferred Or Immediate?
Postgres allows to create DEFERRABLE or NOT DEFERRABLE (immediate) constraints. This means, that any constraint violation
checks of constraints including UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) will not be raised until the
transaction is about to be committed, resulting in the possibility to handle deadlock relations in a graceful way. If we're
able to create both account and profile records, we'll end up with no constraint violations upon committing the transaction,
since no constraints are violated!
To create a deferrable constraint, we'll have to change our constraint creation syntax from
CREATE TABLE "account" (
-- ...
FOREIGN KEY("id") REFERENCES "profile"("id")
);
to
CREATE TABLE "account" (
-- ...
FOREIGN KEY("id") REFERENCES "profile"("id") DEFERRABLE INITIALLY DEFERRED
);
In addition to the DEFERRABLE, which makes our foreign key able to be deferred, or executed immediately as usual,
the INITIALLY DEFERRED will set our default preference to using this constraint in deferred mode, rather than raising
constraint violations immediately when executing a transaction statement. Another option would be to set it to
INITIALLY IMMEDIATE, the default option, which will immediately raise any constraint violation once the statement
is executed, not waiting for the end of the transaction.
🗑 Cleanup, Made Easy
If we were to delete an account, be it in deferred or immediate mode, we'd have to make sure to delete both the account
and profile record, as we wouldn't want to maintain any data we'd want to be deleted. In this case, deferrable constraints
don't do a lot for us, though allowing us to send two statements to clean up both tables' records instead of failing at the
first DELETE statement due to our deadlock relation.
Luckily, Postgres has us covered here again: We can change the constraint to register a policy to handle deletes of the account record
by deleting the related profile with it. This is as simple as adding an ON DELETE CASCADE to the foreign key on the profile table.
CREATE TABLE "profile" (
-- ...
FOREIGN KEY("id") REFERENCES "account"("id")
DEFERRABLE INITIALLY DEFERRED ON DELETE CASCADE
);
We might also want to prevent the profile from being deleted altogether, as the actual delete should be executed on an account.
This can be realized simply by adding ON DELETE RESTRICT to the foreign key on the account table.
🚨 Alerting Early
In special cases, we might want to know whether a deferred constraint would be violated by an action we're about to take, and we'd
like to know immediately, not at the end of our transaction. Once again, Postgres has us covered. The SET CONSTRAINTS command
allows exactly this: You can specify a known constraint by name or simply toggle all constraints to immediate for the scope of
the transaction. To get the most control over which constraints are currently deferred and which are run immediately on
execution, we should refer to our constraint by name.
SET CONSTRAINTS constraint_name IMMEDIATE;
As you can see, designing deadlock relations makes heavy use of various constraint features Postgres has to offer, allowing us to add one workflow on top of another. Once done, we end up with a strict relationship between accounts and profiles, giving guarantees as to which may exist under certain conditions.
I hope you enjoyed this post and learned a thing or two about relations in Postgres. If you've got any questions, suggestions or any feedback at all, don't hesitate to reach out on Twitter or by mail.