0

I have two tables which are linked by a foreign key.

CREATE TABLE clients (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name character varying,
    phone character varying,
    contact text
);
CREATE TABLE address (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    line1 character varying,
    city character varying,
    state text,
    zip character varying,
    uuid integer REFERENCES clients(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
);

My lambda server receives data which should be put into both tables. However the queries I am trying to run are inserting a NULL value into the uuid foreign key column of my address table.

BEGIN; 
INSERT INTO clients (name, phone, contact) 
VALUES ('Trevor J','6077730',''); 

INSERT INTO address (line1, city, state, zip) 
VALUES ('82 Haon Street,'Gasto','OH', 51113); 
COMMIT;

What am I missing to get the proper foreign key in my table?

1 Answer 1

0

If you are only inserting a single row, and there is no other INSERT between the two INSERTs, the easiest way is to use lastval() to get the last generated sequence value (which will be the id generated for the clients table)

INSERT INTO clients (name, phone, contact) 
VALUES ('Trevor J','6077730',''); 

INSERT INTO address (line1, city, state, zip, "uuid") 
VALUES ('82 Haon Street','Gasto','OH', 51113, lastval()); 

This is safe for concurrent transactions.

3
  • Could you use a CTE for the first INSERT with a RETURNING clause and then use that for the second INSERT?
    – Vérace
    Sep 10, 2022 at 19:01
  • @a_horse_with_no_name would this work if I have a third table which also links to the clients table by a foreign key? Or will lastval() take the id of the address table
    – tdammon
    Sep 10, 2022 at 21:02
  • @tdammon: lastval() reflects the value from the most recently used sequence. So yes, it would contain the value from the address ID
    – user1822
    Sep 10, 2022 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.