Inserting into multiple tables with a foreign compound key

Question

I have these two table that I would like to insert alot of data into.

CREATE TABLE IF NOT EXISTS N
(
    id                      INT GENERATED ALWAYS AS IDENTITY,
    name varchar(50)
);
CREATE TABLE IF NOT EXISTS ConnectionProps
(
    connectionProp  INT,
    FK_n INTEGER,
    PRIMARY KEY (connectionProp,FK_n),
    CONSTRAINT FK_n
        FOREIGN KEY(id)
            REFERENCES N(id)
                ON DELETE CASCADE
);

I need to insert into both tables at the same time as the primary key of the connectionprops table is a compound key of the foreignkey of N and connectionProps. How do I do this?

Answer 1

Use a CTE and INSERT ... RETURNING:

WITH x AS (
   INSERT INTO n (name) VALUES ('Willi')
   RETURNING id
)
INSERT INTO connectionprops (connectionprop, fk_n)
SELECT 42, id FROM x;

Answer 2

You can do following

Where currval('N_id_seq')holds the last inserted id.

you had some small errors in your table definitions which i corrected to get a sample

CREATE TABLE IF NOT EXISTS N
(
    id                      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name varchar(50)
);

CREATE TABLE IF NOT EXISTS ConnectionProps
(
    connectionProp  INT,
    FK_n INTEGER,
    PRIMARY KEY (connectionProp,FK_n),
    CONSTRAINT FK_n1
        FOREIGN KEY(FK_n)
            REFERENCES N(id)
                ON DELETE CASCADE
);

✓

BEGIN;
INSERT INTO N ("name")  VALUES('test');
INSERT INTO ConnectionProps (connectionProp,FK_n) VALUES (1,currval('N_id_seq'));
COMMIT;

✓

1 rows affected

1 rows affected

✓

SELECt * FROM ConnectionProps

connectionprop | fk_n
-------------: | ---:
             1 |    1

db<>fiddle here