设置可延迟约束在 PostgreSQL 事务上不起作用
情况是这样的：我有两个表，其中一个引用另一个（例如，table2 引用 table1）。创建这些表时，我确实将外键约束设置为 DEFERRABLE，将 ON UPDATE 和 ON DELETE 子句设置为 NO ACTION（这是默认值）。
但是，当运行下面的事务时，我仍然收到以下错误。
事务：
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE table1 SET blah blah;
UPDATE table2 SET blah blah;
COMMIT;
错误：
ERROR: update or delete on table "table1" violates foreign key constraint "table1_column_fkey" on table "table2"
DETAIL: Key (column1)=(blahblah) is still referenced from table "table2".
和表构造：
CREATE TABLE table1(
column1 CHAR(10),
[...]
PRIMARY KEY (column1)
);
CREATE TABLE table2(
primkey CHAR(9),
[...]
column2 CHAR(10) NOT NULL,
PRIMARY KEY(primkey),
FOREIGN KEY(column2) REFERENCES table1(column1) DEFERRABLE
);
我想要做的是在事务进行时推迟外键检查，直到它提交。我只是不明白为什么会返回此错误以及如何使交易正常进行。
This is the situation: I have two tables where the one references the other (say, table2 references table1). When creating these tables, I did set the foreign key constraint as DEFERRABLE and the ON UPDATE and ON DELETE clauses as NO ACTION (which is the default).
But still, when running the transaction below, I get the following error.
Transaction:
START TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE table1 SET blah blah;
UPDATE table2 SET blah blah;
COMMIT;
Error:
ERROR: update or delete on table "table1" violates foreign key constraint "table1_column_fkey" on table "table2"
DETAIL: Key (column1)=(blahblah) is still referenced from table "table2".
And table construction:
CREATE TABLE table1(
column1 CHAR(10),
[...]
PRIMARY KEY (column1)
);
CREATE TABLE table2(
primkey CHAR(9),
[...]
column2 CHAR(10) NOT NULL,
PRIMARY KEY(primkey),
FOREIGN KEY(column2) REFERENCES table1(column1) DEFERRABLE
);
What I want to do is to defer the foreign key checking while the transaction is in progress, until it commits. I just can't see why is this error returning and how can I make the transaction work.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
该问题确实是违反外键约束。我的意思是，约束确实在事务中被推迟，但问题是在事务结束时，在更新 table1 和 table2 后，新数据违反了外键约束。我正在更新 table1 行的主键，该行仍被某些 table2 行引用。我也必须更新这些行，以便 table2 行的引用列与 table1 行的更新主键匹配。我更改了事务中的“更新”查询，问题得到了解决。
很抱歉让你陷入这样的境地。解决方案是如此简单，但那天我看不到它。
The problem was indeed a foreign key constraint violation. I mean, the constraints were indeed deferred within the transaction, but the problem was that at the end of the transaction, after table1 and table2 were updated, the new data were violating a foreign key constraint. I was updating the primary key of a table1 row, which was still being referenced by some table2 rows. These rows I had to update them too, so that the referencing column of table2 rows matched the updated primary key of table1's row. I changed the 'UPDATE' queries within the transaction and the problem got solved.
Sorry to put you into this. The solution was so simple, but that day I coudn't see it.