How can I insert autogenerated key in a foreign key field in a transaction?

user_18121286 0
Viewed 94

I want to propagate an autogenerated primary key (serial) from my Table CONTACT, to my other table TELEPHONE as foreign key. But i want to do this in a Transaction, in my domain I can't memorize a contact without a number associated to it.

BEGIN;

    DECLARE
    Contact_ID Contact.ID%TYPE;

    INSERT INTO CONTACT (LNAME)
    VALUES ('inserting a name') RETURNING ID INTO Contact_ID;
    --here an ID should be generated in Contact, that has an ID serial
    INSERT INTO TELEPHONE (NUMBER, Contact_reference)
    VALUES ('inserting a number', Contact_ID);

COMMIT;

Apparently, it's wrong to use DECLARE here, in the transaction, but i need that variable. I tried anyway without variables, but still can't run the transaction. I know this is possible in java with JDBC, is there a way i can do this using the Database's language?

PS: Sorry for my bad english.

sql-insert postgresql foreign-keys
1 Answers

Use a data modifying CTE to do it in a single statement:

WITH c AS (
   INSERT INTO CONTACT (LNAME)
   VALUES ('inserting a name')
   RETURNING id
)
INSERT INTO TELEPHONE (NUMBER, Contact_reference)
SELECT 'inserting a number', id
FROM c;
user_64643080
Related