How to Insert with Circular References & Constraints using PostgreSQL

12 Apr 2020 - PostgreSQL, SQL

Recently, while using my favorite database, I faced a SQL issue while trying to insert rows in two tables referencing each other with NOT NULL & Foreign Key constraints while using a single transaction.

Context

Let’s assume the following schema (tested with PostgreSQL 12):

1
2
3
4
5
6
7
8
CREATE TABLE versions(id INT PRIMARY KEY);

CREATE TABLE projects(
  id INT PRIMARY KEY,
  latest_version_id INT NOT NULL REFERENCES versions(id)
);

ALTER TABLE versions ADD COLUMN project_id INT NOT NULL REFERENCES projects(id);

As you can see the tables are referencing each other and at that point it is not possible to create a new project with an associated version in a single transaction as constraints are immediately checked by default:

1
2
3
4
5
6
7
BEGIN;

-- let's insert NULL for now & update it later in the same transaction
INSERT INTO projects VALUES (123, NULL);

-- ERROR:  null value in column "latest_version_id" violates not-null constraint
-- DETAIL:  Failing row contains (123, null).

Deferred constraints

As you may know, it is possible to deffer constraints checks until the end of the transaction. Let’s recreate our schema:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- clean previous work: DROP TABLE projects, versions;

CREATE TABLE versions(id INT PRIMARY KEY);
CREATE TABLE projects(id INT PRIMARY KEY);

ALTER TABLE projects ADD COLUMN latest_version_id INT NOT NULL REFERENCES versions(id)
  DEFERRABLE INITIALLY IMMEDIATE;

ALTER TABLE versions ADD COLUMN project_id INT NOT NULL REFERENCES projects(id)
  DEFERRABLE INITIALLY IMMEDIATE;

Following the SQL standard we should now be able to insert a project & a version in a single transaction:

1
2
3
4
5
6
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO projects VALUES (123, NULL);

-- ERROR:  null value in column "latest_version_id" violates not-null constraint
-- DETAIL:  Failing row contains (1, null).

😱It seems that PostgreSQL does not follow the standard for NOT NULL constraints. And we can validate that by looking at the documentation, Compatibility section:

This command complies with the behavior defined in the SQL standard, except for the limitation that, in PostgreSQL, it does not apply to NOT NULL and CHECK constraints. Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.

Fortunately there is a simple workaround. As only the NOT NULL constraint is not deferred we can use a temporary value for the foreign key until we have the final value:

1
2
3
4
5
6
7
8
9
BEGIN;
SET CONSTRAINTS projects_latest_version_id_fkey, versions_project_id_fkey DEFERRED;

-- not null but *temporary* value
INSERT INTO projects (id, latest_version_id) VALUES (123, -1);
INSERT INTO versions (id, project_id) VALUES (456, 123);
UPDATE projects SET latest_version_id = 456 WHERE id = 123;

COMMIT;

Thank you, Alexis, for your help on this issue 🙌.

Common Table Expressions (Added 2020-04-18)

While browsing resources about deferrable constraints I found this great & in-depth article. As mentioned we can get rid of them by using CTE:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- clean previous work: DROP TABLE projects, versions;

CREATE TABLE versions(id INT PRIMARY KEY);

CREATE TABLE projects(
  id INT PRIMARY KEY,
  latest_version_id INT NOT NULL REFERENCES versions(id)
);

ALTER TABLE versions ADD COLUMN project_id INT NOT NULL REFERENCES projects(id);

-- insert using a single statement
WITH
  new_version_id AS (
    INSERT INTO versions VALUES (1, 2)
  )
INSERT INTO projects VALUES (2, 1);