idiomatic way to atomically create a table that as a record that is associated to other tables-CodePudding

I am coming from graph databases and postgres is still super foreign to me.

I have the following tables

CREATE TYPE runnerenum AS ENUM ('runner');

CREATE TABLE IF NOT EXISTS collections (
  collectionid UUID PRIMARY KEY,
  name VARCHAR(256) UNIQUE NOT NULL,
  runner runnerenum NOT NULL,
  runconfig JSONB 
);

CREATE TABLE IF NOT EXISTS namedexprs(
  namedexprid UUID PRIMARY KEY,
  name VARCHAR(256) UNIQUE NOT NULL,
  -- exprid UUID NOT NULL REFERENCES expressions(exprid),
  collectionid UUID NOT NULL REFERENCES collections(collectionid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS expressions(
  exprid UUID PRIMARY KEY,
  ast JSONB NOT NULL,
  namedexprid UUID NOT NULL REFERENCES namedexprs(namedexprid) ON DELETE CASCADE
);

My question is what is the idiomatic way to create a collections atomically (while also creating associated expressions and namedexprs). Currently I am executing three separate queries and getting errors because of a foreign key violation.

CodePudding user response：

Example of using DEFERRABLE:

CREATE TABLE parent_tbl (
    parent_id integer PRIMARY KEY,
    parent_val varchar UNIQUE
);


CREATE TABLE child_tbl (
    child_id integer PRIMARY KEY,
    parent_fk varchar REFERENCES parent_tbl (parent_val) 
        ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
    child_val varchar
);



\d child_tbl
                    Table "public.child_tbl"
  Column   |       Type        | Collation | Nullable | Default 
----------- ------------------- ----------- ---------- ---------
 child_id  | integer           |           | not null | 
 parent_fk | character varying |           |          | 
 child_val | character varying |           |          | 
Indexes:
    "child_tbl_pkey" PRIMARY KEY, btree (child_id)
Foreign-key constraints:
    "child_tbl_parent_fk_fkey" FOREIGN KEY (parent_fk) REFERENCES parent_tbl(parent_val) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED

BEGIN;
INSERT INTO child_tbl VALUES (1, 'dog', 'cat');

SELECT * FROM child_tbl ;
 child_id | parent_fk | child_val 
---------- ----------- -----------
        1 | dog       | cat
(1 row)

SELECT * FROM parent_tbl ;
 parent_id | parent_val 
----------- ------------
(0 rows)

INSERT INTO parent_tbl VALUES (1, 'dog');

SELECT * FROM parent_tbl ;
 parent_id | parent_val 
----------- ------------
         1 | dog

COMMIT;

The key to using DEFERRABLE is that the individual data entry statements need to be bundled into the same transaction, the BEGIN;/COMMIT;. This allows DEFERRABLE INITIALLY DEFERRED to work as the constraint check is deferred until the end of the transaction. For more ways you can manipulate this see SET CONSTRAINTS.