Insert Data Into Tables Linked by Foreign Key

Question

I am using PostgreSQL   Customer                    Customer ID   Name  Order                                Order ID   Customer ID   Price   To insert an order  here is what I need to do usually   For example   John  place  1 34  priced order    1  Get Customer ID from Customer table  where name is  John   2  If there are no Customer ID returned  There is no John   insert  John   3  Get Customer ID from Customer table  where name is  John   4  Insert  Customer ID  and  1 34  into Order table    There are 4 SQL communication with database involved for this simple operation     Is there any better way  which can be achievable using 1 SQL statement

User · Answer

Not with a regular statement  no   What you can do is wrap the functionality in a PL pgsql function  or another language  but PL pgsql seems to be the most appropriate for this   and then just call that function  That means it ll still be a single statement to your app

User · Answer

You can do it in one sql statement for existing customers  3 statements for new ones   All you have to do is be an optimist and act as though the customer already exists   insert into  order   customer id  price  values     select customer id from customer where name    John    12 34     If the customer does not exist  you ll get an sql exception which text will be something like   null value in column  customer id  violates not-null constraint    providing you made customer id non-nullable  which I m sure you did    When that exception occurs  insert the customer into the customer table and redo the insert into the order table   insert into customer name  values   John    insert into  order   customer id  price  values     select customer id from customer where name    John    12 34     Unless your business is growing at a rate that will make  where to put all the money  your only real problem  most of your inserts will be for existing customers   So  most of the time  the exception won t occur and you ll be done in one statement

User · Answer

Use stored procedures   And even assuming you would want not to use stored procedures - there is at most 3 commands to be run  not 4  Second getting id is useless  as you can do  INSERT INTO     RETURNING

[sql] Insert Data Into Tables Linked by Foreign Key

Examples related to sql

Examples related to postgresql