PostgreSQL 16: WITH/CTE versus Non-Deferrable Constraints

I am using PostgreSQL 16 upwards. I am currently trying to create an example for a relationship of type `G-|o-----|= 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.