PostgreSQL 触发器中的唯一约束冲突

Question

我有以下两个表格。我在员工表上插入触发器后写的。如果我在员工表中插入记录，它将employee_audits表中插入记录。

两个表都有主键列 (id(。 假设如果您尝试插入 Employees 表中不存在且存在于employee_audits表中的记录 ID 值，则会显示 错误重复键值违反了唯一约束"employee_audits_pkey"，并且它没有在员工表中插入记录。两个事务都失败了。 但是我想在员工表中插入记录。

CREATE TABLE employees(
id SERIAL PRIMARY KEY,
first_name VARCHAR(40) NOT NULL,
last_name VARCHAR(40) NOT NULL
);
CREATE TABLE employee_audits (
id SERIAL PRIMARY KEY,
last_name VARCHAR(40) NOT NULL,
changed_on TIMESTAMP(6) NOT NULL
)

触发功能：

CREATE OR REPLACE FUNCTION log_last_name_changes()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO employee_audits(last_name,changed_on)
VALUES(NEW.last_name,now());
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

触发器定义：

CREATE TRIGGER last_name_changes
AFTER INSERT
ON employees
FOR EACH ROW
EXECUTE PROCEDURE log_last_name_changes();

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe');
SELECT * FROM EMPLOYEES

id first_name   last_name
1  "John"        "Doe"

SELECT * FROM EMPLOYEE_AUDITS

ID   last_name  CHANGED_ON
1      "Doe"    "2019-12-27 17:21:13.934"

手动插入第二个工作台

insert into employee_audits values(2,'banu','2019-12-27 17:21:13.934')

第一个工作台上的手动插入

INSERT INTO employees (first_name, last_name)
VALUES ('David', 'Raj');

错误重复键值违反唯一约束"employee_audits_pkey">

是否可以在员工表中插入记录？

Answer 1

如果您有serial列，则永远不应手动为其提供值。手动为serial提供值不会推进该列后面的序列，因此下次插入而不指定id列时，将采用下一个序列值，该值2，因为序列仅前进一次。

所以代替：

insert into employee_audits values(2,'banu','2019-12-27 17:21:13.934')

只需使用：

insert into employee_audits (last_name, changed_at)
values ('banu','2019-12-27 17:21:13.934');

serial列的这种行为是强烈建议在现代 Postgres 版本中使用identity列的原因之一。