SQL 将数据插入到外键链接的表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1997998/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert Data Into Tables Linked by Foreign Key
提问by Cheok Yan Cheng
I am using PostgreSQL.
我正在使用 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.
例如，“John”下“1.34”定价的订单。
(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!!!
这个简单的操作涉及到4个SQL与数据库的通信！！！
Is there any better way, which can be achievable using 1 SQL statement?
有没有更好的方法可以使用1条SQL语句实现？
回答by Wayne Conrad
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:
您可以在现有客户的 1 条 sql 语句中完成，为新客户使用 3 条语句。您所要做的就是做一个乐观主义者，并表现得好像客户已经存在一样：
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:
如果客户不存在，您将收到一个 sql 异常，其文本类似于：
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:
（前提是您将 customer_id 设为不可为空，我确定您做到了）。发生该异常时，将客户插入客户表并重新插入订单表：
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.
除非您的业务增长速度使“把所有钱放在哪里”成为您唯一真正的问题，否则您的大部分插入内容将是针对现有客户的。因此，大多数情况下，异常不会发生，您将在一个语句中完成。
回答by Magnus Hagander
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.
您可以做的是将功能包装在 PL/pgsql 函数（或其他语言，但 PL/pgsql 似乎最适合此功能）中，然后调用该函数。这意味着它仍然是对您的应用程序的单个语句。
回答by Magnus Hagander
Use stored procedures.
使用存储过程。
And even assuming you would want not to use stored procedures - there is at most3 commands to be run, not 4. Second getting id is useless, as you can do "INSERT INTO ... RETURNING".
即使假设您不想使用存储过程 -最多只能运行 3 个命令，而不是 4 个。第二个获取 id 是无用的，因为您可以执行“INSERT INTO ... RETURNING”。
