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

Question

我有下面两个表。我在 employees 表上插入触发器后写了。如果我在 employees 表中插入记录，它将在 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');

Error duplicate key value violates unique constraint "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 列的这种行为是它是 highly recommended 的原因之一。在现代 Postgres 版本中使用 identity 列。