Insert Data Into Tables Linked by Foreign Key

Question

Hello all, 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?

Answer 1

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.

Answer 2

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".

Answer 3

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.