同时更新多条记录上的关键约束

Question

正如@Tony‘’Hagan here所提到的，我们有一个具有唯一键的表，该表通过“老化”旧记录进行更新。

该表如下：

-- auto-generated definition
create table abc
(
    key   uuid    not null,
    hash  text    not null,
    age   integer not null,
    value varchar(50),
    constraint abc_pkey
        primary key (key, age)
);

我们可以使用以下虚拟数据模拟“老化”记录：

INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 0, '1');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 1, '2');
INSERT INTO public.abc (key, hash, age, value) VALUES ('bec619bb-451c-49d8-b555-4d16e1f724fb', 'asdf', 2, '3');

当我想要添加一个新记录时，我必须先“老化”旧记录，然后再用age=0插入一个新记录。

但是，当我运行下面的查询时，会得到以下错误消息：

[23505] ERROR: duplicate key value violates unique constraint "abc_pkey" Detail: Key (key, age)=(bec619bb-451c-49d8-b555-4d16e1f724fb, 2) already exists.

UPDATE abc
SET age = age +1
WHERE key IN (
    'bec619bb-451c-49d8-b555-4d16e1f724fb'
    )

我如何更新/老化这些记录？

Answer 1

我们可以使用命令禁用约束。

SET CONSTRAINTS ALL DEFERRED

✓

它允许我们运行更新程序

UPDATE public.abc SET age = age + 1;

3行影响

然后，我们可以用以下方法重新激活约束

SET CONSTRAINTS ALL IMMEDIATE

✓