将数据插入到由外键链接的表中 [英] Insert Data Into Tables Linked by Foreign Key

问题描述

我正在使用PostgreSQL。

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,

例如，约翰下达 1.34价格的订单。

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.

此简单操作涉及与数据库的4条SQL通信！

There are 4 SQL communication with database involved for this simple operation!!!

有没有更好的方法，可以使用1条SQL语句实现？

Is there any better way, which can be achievable using 1 SQL statement?

推荐答案

您对于现有客户，可以在一个sql语句中完成，对于新客户，可以在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);

如果客户不存在，您将收到一个sql异常，其文本将类似于：

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

（假设您将customer_id设置为不可为空，那么我确定您做到了）。发生该异常时，将客户插入客户表，然后将插入重做到订单表中：

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

这篇关于将数据插入到由外键链接的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！