I have two tables whose foreign refer to each other, how to persist them?

Question

I have two tables, whose foreign refer to each other, I learned from one article that insert script on tables won't work without deferrable in such a scenario, but I am surprised how the same is being achieved in the spring application without deferrable on table structure.

Please refer to the links below: https://www.alibabacloud.com/blog/postgresql-deferrable-constraints-unique-primary-key-foreign-key-and-exclude_597717#:~:text=If%20a%20constraint%20is%20deferrable,the%20end%20of%20the%20transaction.

Create two tables whose foreign keys reference each other

Select only one DBMS and remove excess tags. – Akina May 06 '22 at 05:07 — Akina, May 06 '22 at 05:07

score 1 · Answer 1 · answered May 06 '22 at 05:44

In Postgres, non-deferrable constraints are checked after each command. So insert mutually referencing rows in a single command: CTEs are considered to be part of single command.

WITH ins1 AS (
   INSERT INTO tbl1(tbl1_id, tbl2_id) VALUES(2, 3)
   )
INSERT INTO tbl2(tbl2_id, tbl1_id) VALUES(3, 2);

(Deferable constraints allow to postpone the check until the end of the transaction. But they are more expensive.)

See:

I have two tables whose foreign refer to each other, how to persist them?

1 Answers1