PostgreSQL 16: WITH/CTE versus Non-Deferrable Constraints

Question

I am using PostgreSQL 16 upwards. I am currently trying to create an example for a relationship of type G-|o-----|<-H in Crow's foot lingo. This means that each row in the table g for entity type G must be related to at least one row in the table h for entity type H, but maybe more than one. This is the important part: There must not be any row in g unrelated to a row in h. Each row in the table for entity type H can be related to zero or one row in the table for entity type G.

This is basically an academic exercise. I want to absolutely enforce this relationship pattern.

I came up with the following structure:

/* Create the tables for a G-|o-----|<-H relationship. */
-- Table G: Each row in G is related to one or multiple rows in H.
CREATE TABLE g (
 id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 h INT NOT NULL, -- later used to reference relate_g_and_h
 x CHAR(3) -- example for other attributes
);
-- Table H: Each row in H is related to zero or one row in G.
CREATE TABLE h (
 id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 y CHAR(2) -- example for other attributes
);
-- The table for managing the relationship between G and H.
CREATE TABLE relate_g_and_h (
 g INT NOT NULL REFERENCES g (id),
 h INT NOT NULL UNIQUE REFERENCES h (id),
 PRIMARY KEY (g, h)
);
-- To table G, we add the foreign key reference constraint towards
-- table relate_g_and_h. This enforces that one relation must exist.
ALTER TABLE g ADD CONSTRAINT g_id_h_fk FOREIGN KEY (id, h)
 REFERENCES relate_g_and_h (g, h);

I think that this basically enforces such a relationship, although it is not very beautiful. It also seems to work well.

I can insert data as follows:

/* Insert into tables for G-|o-----|<-H relationship. */
-- Insert some rows into the table for entity type H.
INSERT INTO h (y) VALUES ('AB'), ('CD'), ('EF'), ('GH');
-- Insert into g and relate_g_and_h at the same time(?)
WITH g_id AS (INSERT INTO g (x, h) VALUES ('123', 1) RETURNING id)
 INSERT INTO relate_g_and_h (g, h) SELECT id, 1 FROM g_id;
WITH g_id AS (INSERT INTO g (x, h) VALUES ('789', 4) RETURNING id)
 INSERT INTO relate_g_and_h (g, h) SELECT id, 4 FROM g_id;
-- The second relation between the first G entity and a H entity can be
-- inserted much more easily.
INSERT INTO relate_g_and_h VALUES (1, 2);
-- Combine the rows from G and H. This needs two INNER JOINs.
SELECT g.x, h.y FROM g
 INNER JOIN relate_g_and_h ON g.id = relate_g_and_h.g
 INNER JOIN h ON h.id = relate_g_and_h.h;

Originally, I thought I should do these first inserts in transactions and declare the constraints as DEFERRABLE. This works. However, in the above example, it also works without that. I did not declare the constraints as DEFERRABLE. I am not using a transaction (or maybe implicitly? I am really not sure.)

What certainly fails is this:

INSERT INTO g (x, h) VALUES ('123', 1); -- gets id 1
INSERT INTO relate_g_and_h (g, h) VALUES (1, 1);

So I feel that the WITH method looks OK. However, I am not fully sure whether it is. I did not see when constraints are evaluated in the documentation. I am afraid that the queries only work because of some sort of concurrency issue and that they might as well fail.

Therefore: Could anybody with PostgreSQL experience verify whether this approach is OK or whether there are concurrency or other issues with it? Pointers to the documentation that clarify my question would be very very much appreciated.

Many thanks, Thomas.

---

After some thinking, I also came up with a possible two-table solution. Here, I create the tables as follows:

/* Create the tables for a G-|o-----|<-H relationship. */
-- Table H: Each row in H is related to zero or one rows in G.
CREATE TABLE h (
 id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 g INT, -- Each row related to 0 or 1 G (constraint at bottom).
 y CHAR(2), -- example for other attributes
 CONSTRAINT h_id_g_uq UNIQUE (id, g) -- Needed for use in fk in g.
);
-- Table G: Each row in G is related to one or multiple rows in H.
-- We force that that row in H is also related to our row in G via
-- a foreign key constraint g_h_fk.
CREATE TABLE g (
 id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 h INT NOT NULL REFERENCES h (id), -- Each row is related >= 1 H.
 x CHAR(3), -- example for other attributes
 CONSTRAINT g_h_fk FOREIGN KEY (h, id) REFERENCES h (id, g)
);
-- To table H, we add the foreign key reference constraint towards g.
ALTER TABLE h ADD CONSTRAINT h_g_fk FOREIGN KEY (g) REFERENCES g (id);

Then I can insert data into the tables and read them back out as follows:

/* Insert into tables for G-|o-----|<-H relationship. */
-- Insert some rows into the table for entity type H.
-- Not specifying `g` leave the references G as NULL for now.
INSERT INTO h (y) VALUES ('AB'), ('CD'), ('EF'), ('GH'), ('IJ');
-- Insert into G and relate to H. We do this three times.
WITH g_id AS (INSERT INTO g (h, x) VALUES (1, '123') RETURNING id)
 UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 1;
WITH g_id AS (INSERT INTO g (h, x) VALUES (3, '456') RETURNING id)
 UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 3;
WITH g_id AS (INSERT INTO g (h, x) VALUES (4, '789') RETURNING id)
 UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 4;
-- Link one H row to another G row. (We do this twice.)
UPDATE h SET g = 3 WHERE id = 2;
UPDATE h SET g = 3 WHERE id = 5;
-- Combine the rows from G and H.
SELECT g.id AS g_id, g.x, h.id AS h_id, h.y FROM h
 INNER JOIN g ON g.id = h.g;

This still requires the use of Common Table Expressions. However, by now, I am fairly confident that this is OK. Still, I am not 100% sure.

I think both approaches do work and I could not find an error with either of them. But the two table method is probably more efficient and more elegant. @Akina was right.

• Akina: one-to-many does not need in separate relation table. In your case g is a property of h
• Thomas Weise: And then how do you enforce that each g is connected to at least one h? How do you prevent that someone inserts a row into g that is not connected to any row in h?
• Thomas Weise: To clarify: The whole purpose of this exercise is to enforce the optionally-one to mandatory-many relationship. This is not about what is practical or whether it is necessary or even a good idea to implement such a relationship. It is about how to do it. I would prefer if it could be done with pure SQL, but using PostgresSQL extensions to SQL is also OK. The purpose is to do it correctly and completely and without error and, ideally, have it backed up by the PostgreSQL documentation.
• Akina: And then how do you enforce that each g is connected to at least one h? This needs in deferred constraint, no alternative. Your trick with h INT NOT NULL, -- later used to reference relate_g_and_h is bad solution because it does not prevent opened relation chain.
• Thomas Weise: I do not understand your comment. Could you put your suggestion into SQL code, like in my example? (Also, please notice that I do add a constraint later via ALTER TABLE at the bottom of my SQL. So the NOT NULL does not stand alone.)