设置可延迟的约束不适用于PostgreSQL事务 [英] Setting constraint deferrable doesn't work on PostgreSQL transaction

问题描述

这是这种情况:我有两个表，其中一个引用另一个表(例如，table2引用table1).创建这些表时，我确实将外键约束设置为DEFERRABLE，并将ON UPDATE和ON DELETE子句设置为NO ACTION(默认设置).

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.

交易:

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
);

我要做的是在事务进行过程中推迟外键检查，直到提交为止.我只是看不到为什么会返回此错误，以及如何使交易正常进行.

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.

推荐答案

问题确实是违反外键约束的.我的意思是，约束确实在事务中被延迟了，但是问题是，在事务结束时，在更新表1和表2之后，新数据违反了外键约束.我正在更新一个table1行的主键，该键仍被某些table2行引用.这些行我也必须对其进行更新，以便table2行的引用列与table1行的更新后的主键匹配.我更改了事务中的"UPDATE"查询，问题得以解决.

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.

这篇关于设置可延迟的约束不适用于PostgreSQL事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！