You might have experienced a scenario where a traditional foreign key constraint would be violated, for example, when creating circular references or having multiple entities depend on a resource where all rows are dropped at once. Sometimes, rows referencing deleted or updated entities should stay around, sometimes, the deletion process should cascade and include all referencing rows, too.
PostgreSQL offers a wide range of options for foreign keys, including marking foreign key constraints as deferrable in complex cases. In this guide, we’ll walk through all options you have when configuring foreign key constraints, how you can detect the mode an existing foreign key constraint is set to, and how to model your data around these concepts.
For the remainder of the guide, we’ll walk over most parts of the foreign key creation grammar, so it might be helpful to keep the following in mind
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
A brief recap on Foreign Keys
Foreign key constraints are the standard measure to ensure referential integrity in relational databases. If this sounded a bit too academic, let’s break down every part of this statement.
Constraints are, as the name says, ways to constrain data in your database to match your expectations. In addition to foreign keys, there are primary keys that uniquely identify a given row, unique constraints ensuring uniqueness, not-null, and check constraints.
Foreign keys are helpful when you reference data living in another table or row. Usually, foreign keys are used when you normalize your data model to live in multiple tables.
As an example, let’s consider the case of modeling Post and Author, which live in different tables, where the Post table has an author column referencing the Author by its primary key.
CREATE TABLE "author" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"name" varchar(256) NOT NULL
);
CREATE TABLE "post" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"author" varchar(64) NOT NULL,
"title" varchar(512) NOT NULL,
"content" text NOT NULL,
CONSTRAINT "post_author_fkey" FOREIGN KEY ("author") REFERENCES "author" ("id")
);
In this example, the author column has a not-null constraint, ensuring there’s always an Author connected to the Post. The last constraint specifies a foreign key, which then ensures every value in the author column references an existing row identified by the id column of Author. When defining the foreign key constraint, you can only reference columns that are primary keys or form a unique constraint on the reference table.
Now, this is great and all, but what if you delete an author? Should we also drop their posts? Or should we raise an error so that the application is required to drop all posts linked to an author before the author can be deleted? These decisions can be modeled with referential actions.
Referential Actions (ON UPDATE, ON DELETE)
Referential actions specify the expected behavior when a referenced row is deleted (ON DELETE) or data in the referenced columns is updated (ON UPDATE).
Available actions include
- doing nothing (
NO ACTIONorRESTRICT, which have subtle differences explained later) and failing the statement - cascading rows referencing the deleted row or updating columns referencing the updated column (
CASCADE) - setting the referencing column to null (
SET NULL) - setting the referencing column to their default values (
SET DEFAULT)
You declare these actions when adding the constraint
CREATE TABLE "post" (
...
CONSTRAINT "post_author_fkey"
FOREIGN KEY ("author")
REFERENCES "author" ("id")
ON DELETE CASCADE
);
Timing Constraint Checks
When does PostgreSQL validate that referenced rows do exist? The answer is, as it is so often, it depends. By default, PostgreSQL validates referential integrity after every statement.
This can be changed, however, by using the SET CONSTRAINTS command, which allows setting constraint checking to deferred or immediate for the current transaction.
Deferred constraints are checked at the end of the transaction, while immediate constraints are checked after each statement.
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS "post_author_fkey" DEFERRED;
But there’s a catch: Deferring constraints only works when the foreign key constraints have been marked as deferrable.
In the previous section about referential actions, I said that we’d cover the difference between NO ACTION and RESTRICT in more detail: RESTRICT is the toughest measure to prevent referenced rows from being dropped as the check cannot be deferred. NO ACTION will also raise an error if the referenced value does not exist, but it can be deferred, giving you slightly more freedom when deleting data.
Deferrable Constraints
When creating a constraint, the default behavior is NOT DEFERRABLE, meaning that you can’t defer constraint checks until the end of the transaction. You can, however, specify that a constraint should be DEFERRABLE. In this case, constraints can be deferred, but by default, the constraint checks are IMMEDIATE, so after each statement. This can be set by using INITIALLY DEFERRABLE.
CREATE TABLE "post" (
...
CONSTRAINT "post_author_fkey"
FOREIGN KEY ("author")
REFERENCES "author" ("id")
ON DELETE CASCADE
DEFERRABLE
INITIALLY DEFERRED
);
You can also change constraint deferrability later on
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
If we wanted to modify our example foreign key after creating the initial, non-deferrable version, we’d run
ALTER TABLE "post" ALTER CONSTRAINT "post_author_fkey" DEFERRABLE;
For most cases, non-deferrable constraints are completely fine, and you might never need to defer constraint checks. But sometimes, you run into issues with foreign key integrity violations, so now you have all the tools (marking constraint as deferrable, deferring constraints, or setting constraint to initially deferred if running SET CONSTRAINTS isn’t feasible).
Next, we’ll check out some use cases for deferrable constraints, followed by a bonus when you need to investigate existing constraints.
Use Cases
Circular References
The classic case for deferred constraints is creating circular references. Without deferred constraints, you could not create a row that depends on another row, which in turn depends on this row existing. With deferred constraints, you can create both rows, with both constraints being checked at the end of the transaction.
Let’s work through a short example for this:
BEGIN;
CREATE TABLE "issue" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"op_ed" varchar(64) NOT NULL
);
CREATE TABLE "story" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"issue" varchar(64) NOT NULL
);
ALTER TABLE "issue" ADD CONSTRAINT "issue_op_ed_fkey"
FOREIGN KEY ("op_ed") REFERENCES "story" ("id") ON DELETE RESTRICT;
ALTER TABLE "story" ADD CONSTRAINT "story_issue_fkey"
FOREIGN KEY ("issue") REFERENCES "issue" ("id") ON DELETE CASCADE;
insert into "issue" values ('26e8vnnqxkQSVjqfkcrErw7kpr7', '26e8yamF07UtUe80ZO7G1OrzSPO');
insert into "story" values ('26e8yamF07UtUe80ZO7G1OrzSPO', '26e8vnnqxkQSVjqfkcrErw7kpr7');
ROLLBACK;
While you can create the tables, trying to add data leads you to a Catch-22, as you cannot insert an issue before creating a story, which in turn depends on an existing issue.
With deferred constraints, there’s a way out:
BEGIN;
CREATE TABLE "issue" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"op_ed" varchar(64) NOT NULL
);
CREATE TABLE "story" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"issue" varchar(64) NOT NULL
);
ALTER TABLE "issue" ADD CONSTRAINT "issue_op_ed_fkey" FOREIGN KEY ("op_ed") REFERENCES "story" ("id") ON DELETE RESTRICT DEFERRABLE;
ALTER TABLE "story" ADD CONSTRAINT "story_issue_fkey" FOREIGN KEY ("issue") REFERENCES "issue" ("id") ON DELETE CASCADE;
SET CONSTRAINTS "issue_op_ed_fkey" DEFERRED;
insert into "issue" values ('26e8vnnqxkQSVjqfkcrErw7kpr7', '26e8yamF07UtUe80ZO7G1OrzSPO');
insert into "story" values ('26e8yamF07UtUe80ZO7G1OrzSPO', '26e8vnnqxkQSVjqfkcrErw7kpr7');
-- check pending constraints
SET CONSTRAINTS "issue_op_ed_fkey" IMMEDIATE;
ROLLBACK;
The only change is to defer the foreign key linking to the op-ed story. This way, granted we know both IDs, we can insert the issue, then the story, which has an existing issue to reference.
Running SET CONSTRAINTS to change the constraint to immediate again has the same effect as reaching the end of the transaction, executing constraint checks to find foreign key integrity violations.
Intertwined Deletes
Imagine the following example, read through it a couple of times, and try to find the issue.
BEGIN;
CREATE TABLE "org" (
"id" varchar(64) NOT NULL PRIMARY KEY
);
CREATE TABLE "team" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"org" varchar(64) NOT NULL REFERENCES "org" ("id") ON DELETE CASCADE
);
CREATE TABLE "member" (
"id" varchar(64) NOT NULL PRIMARY KEY,
"org" varchar(64) NOT NULL REFERENCES "org" ("id") ON DELETE CASCADE,
"team" varchar(64) NOT NULL REFERENCES "team" ("id") ON DELETE RESTRICT
);
insert into "org" values ('26eAkCctuBEztOlCJjWq9jF9A4f');
insert into "team" values ('26eAmEiPSiWyMt1twSpmFzsBGDM', '26eAkCctuBEztOlCJjWq9jF9A4f');
insert into "member" values ('26eAoIGgQT0WKSHlEqrsNASJ0bK', '26eAkCctuBEztOlCJjWq9jF9A4f', '26eAmEiPSiWyMt1twSpmFzsBGDM');
delete from "org";
ROLLBACK;
The issue here is that team members depend on being in a team, so deleting a team with members still assigned to it will fail. That’s usually a great thing, but when you’re deleting an organization, both teams and members would be dropped anyway, so do you still want this constraint?
Interestingly, the way PostgreSQL plans the delete query decides whether all entities are dropped or an error is raised. When the team is dropped first, it will fail. When the members are dropped first, it will succeed.
One way to solve this is to defer the reference to a member’s team. We also need to change the action from RESTRICT to NO ACTION, as RESTRICT cannot be deferred and would still fail, even if we deferred the foreign key constraint!
alter table "member" drop constraint "member_team_fkey";
alter table "member"
add constraint "member_team_fkey"
foreign key ("team")
references "team" ("id")
on delete no action
deferrable initially deferred;
This way, we can drop the organization without worrying about foreign key integrity violations.
Checking constraint configuration
When you’re dealing with existing constraints, you might be curious about the current configuration: Is the foreign key constraint deferrable? Is it immediately deferred?
For this, the information schema can come in handy, showing you is_deferrable and initially_deferred (note: enforced is not available in PostgreSQL, and always YES).
select * from information_schema.table_constraints
where
constraint_type = 'FOREIGN KEY'
and constraint_schema = 'public'
-- and constraint_name = ''
;