如何使子表的外键与父表的主自动增量键具有相同的值 [英] How to get child table's foreign key to have same value as parent's primary auto-increment key

问题描述

我创建了这两个表：

CREATE TABLE Purchase(
purchaseID SERIAL,
custName VARCHAR(30) NOT null,
PRIMARY KEY (purchaseID));

CREATE TABLE PurchasedItem(
purchaseID INT,
itemNo INT NOT NULL,
PRIMARY KEY (purchaseID, itemNo),
FOREIGN KEY (purchaseID) REFERENCES Purchase(purchaseID));

下一步我希望将数据插入两个表中，所购买商品的purchaseID外键具有相同的

Next I wish to insert data into both tables, with the purchaseID foreign key of purchased item having the same value as the purchaseID Serial from Purchase table.

我正在使用一个名为PSequel的PostgreSQL客户端。我尝试在客户端中先将AUTOCOMMIT设置为off，这样我可以在同一事务中使用两个INSERT语句，但是客户端无法识别 autocommit，因此我在终端中尝试了该操作，但我认为它仍然有效... ，这就是我尝试过的两个INSERT语句。

I am using a PostgreSQL client called PSequel. I tried setting AUTOCOMMIT to off first in the client so I could have the two INSERT statement in the same transaction, however the client didn't recognise "autocommit", so I tried it in the terminal and I think it worked... anyway, these are the two INSERT statements I tried.

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) VALUES (DEFAULT, 111);
commit;

但是我得到一个错误：

ERROR: null value in column purchaseID violates not-null constraint.

这是指PurchasedItem的purchaseID，就像运行第一个INSERT语句本身一样。我该如何解决此问题？

this is referring to the PurchasedItem's purchaseID as in running the first INSERT statement by itself it works. How do I solve this problem?

推荐答案

您可以使用 lastval（）

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) VALUES (lastval(), 111);
commit;

或者直接查询基础序列：

Alternatively query the underlying sequence directly:

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) 
VALUES (currval('purchase_purchaseid_seq'), 111);
commit;

或者如果您不想依赖序列的自动命名，请使用 pg_get_serial_sequence 来获取与列关联的序列：

Or if you don't want to rely on the automatic naming of the sequence, use pg_get_serial_sequence to get the sequence associated with the column:

INSERT INTO Purchase(custName) VALUES ('Lendl');
INSERT INTO PurchasedItem(purchaseID, itemNo) 
VALUES (currval(pg_get_serial_sequence('purchase', 'purchaseid')), 111);
commit;

有关更多详细信息，请参见手册：> https://www.postgresql.org/docs/current/static/functions-sequence.html

For more details see the manual: https://www.postgresql.org/docs/current/static/functions-sequence.html

这篇关于如何使子表的外键与父表的主自动增量键具有相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！