问题:CASCADE DELETE 对两个外键约束

我有以下示例:

Table A
   -some_id

Table B
   -another_id

Table C
   -some_id_fk
   -another_id_fk

如果some_id和another_id都从各自的表中删除,我想在Table C上级联一行。

删除两个外键后,如何在表 C 级联中创建一行?

如果仅删除了一个 FK,则受影响的行应在引用该外键的列中更改为空值。

解答

我建议使用两个外键约束ON DELETE SET NULL和一个负责其余部分的触发器

表:

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

扳机:

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<>在这里小提琴

老 sqlfiddle

确保连接表有一个代理 PK 列。(a_id, b_id)无论如何都不能是PK,因为这将不允许两者都为NULL。添加一个UNIQUE约束代替,它允许 NULL 值。

触发器针对性能进行了优化,并且仅在两个 FK 列之一被更新时才会启动,并且只有当这导致两者都是NULL时才会启动。

触发函数删除行并返回NULL以取消现在的void级联UPDATE。