我建议两个外键约束ON DELETE SET NULL https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK以及一个负责其余工作的触发器
Tables:
CREATE TABLE a (a_id serial PRIMARY KEY, a text NOT NULL);
CREATE TABLE b (b_id serial PRIMARY KEY, b text NOT NULL);
CREATE TABLE ab (
ab_id serial PRIMARY KEY
, a_id int REFERENCES a ON DELETE SET NULL
, b_id int REFERENCES b ON DELETE SET NULL
, UNIQUE (a_id, b_id)
);
Trigger:
CREATE OR REPLACE FUNCTION trg_ab_upbef_nulldel()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
DELETE FROM ab WHERE ab_id = NEW.ab_id;
RETURN NULL; -- to cancel UPDATE
END
$func$;
CREATE TRIGGER upbef_null2del
BEFORE UPDATE OF a_id, b_id ON ab
FOR EACH ROW
WHEN (NEW.a_id IS NULL AND NEW.b_id IS NULL)
EXECUTE PROCEDURE trg_ab_upbef_nulldel();
db<>fiddle
Old sqlfiddle http://sqlfiddle.com/#!17/cdcee/1
确保连接表有一个替代 PK 列。(a_id, b_id)
无论如何都不能是 PK,因为这将不允许两者都为 NULL。添加一个UNIQUE约束 https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS相反,它允许 NULL 值。
该触发器针对性能进行了优化,并且仅在两个 FK 列之一更新时才会启动,并且仅当更新导致两者都更新时才会启动。NULL
.
触发函数删除该行并返回NULL以取消now void级联UPDATE
.