PostrgreSQL ForeignKeyViolation-CodePudding

I am attempting to insert some data into my database via a lambda function. I am getting the following error ForeignKeyViolation: insert or update on table "address" violates foreign key constraint "address_id_fkey"

I understand that this is because my address table has a foreign key linking it to the clients table, and the keys are not matching.

Is there a way to format my tables so that I can input my client data and address data together? Or will I need to input the client data first, then retrieve the id and use it to input the address data.

Currently I am running the following two functions.

    postgres_insert_query = "INSERT INTO clients (name, phone, contact) VALUES ('{0}','{1}','{2}')".format(data['name'], data['phone'], data['contact'])
    postgres_insert_query = "INSERT INTO address (line1, city, state, zip) VALUES ('{0}','{1}','{2}', {3})".format(address['line1'], address['city'], address['state'], address['zip'])

Even if no address data is present I would still like to create a row for it (with the correct foreign key).

CodePudding user response：

use DEFERRABLE foreign key constraint. Then wrap you function into a transaction.

CREATE temp TABLE pktable (
    id      INT4 PRIMARY KEY,
    other   INT4
);
CREATE temp TABLE fktable (
id      INT4 PRIMARY KEY,
fk      INT4 REFERENCES pktable DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO fktable VALUES (100, 200);
INSERT INTO pktable VALUES (200, 500); 
COMMIT;

CodePudding user response：

Postgres allows DML operations within a CTE. Doing so will allow you to insert into both tables in a single statement while allowing auto-generation of both ids. The following is a Postgres implementation. See demo.

with thedata(name, phone, contact, line1, city, state, zip) as 
     ( values ('client 1', 'ev4 4213', 'andy','614 a', 'some city;','that state','11111'))
   , theinsert (cli_id) as 
     ( insert into clients(name, phone, contact) 
           select name, phone, contact
             from thedata
         returning cli_id
     ) 
insert into addresses(cli_id, line1, city, state, zip) 
   select cli_id, line1, city, state, zip
     from theinsert 
     cross join thedata;

Unfortunately I do not know your obscurification (Orm) language but perhaps something like:

pg_query = "with thedata( {0} name, {1} phone, {2} contact, {3} line1, {4} city, {5} state, {6} zip) as 
               , theinsert (cli_id) as 
                 ( insert into clients(name, phone, contact) 
                       select name, phone, contact
                         from thedata
                     returning cli_id
                 ) 
            insert into addresses(cli_id, line1, city, state, zip) 
               select cli_id, line1, city, state, zip
                 from theinsert 
                 cross join thedata".format(data['name'], data['phone'], data['contact']
                                    , address['line1'], address['city'], address['state'], address['zip']);