级联删除两个外键约束 [英] CASCADE DELETE on two foreign key constraints

问题描述

我有以下示例：

Table A
   -some_id

Table B
   -another_id

Table C
   -some_id_fk
   -another_id_fk

如果 some_id 和<$ c都想在表C 上级联一行$ c> another_id 从各自的表中删除。

I want to cascade a row on Table C if both some_id and another_id are deleted from their respective tables.

当删除两个外键时，如何在表C中级联自身？

How can I make a row in Table C cascade itself when two foreign keys are deleted?

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

If only one of the FKs is deleted, the affected row should change to a null value in the column referencing that foreign key.

推荐答案

我建议两个带有 ON DELETE SET NULL 的外键约束和一个负责其余操作的触发器

I suggest two foreign key constraints with ON DELETE SET NULL and a trigger that takes care of the rest

表：

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 AS
$func$
BEGIN
DELETE FROM ab WHERE ab_id = NEW.ab_id;
RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_nulldel
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();

SQL小提琴。

• 请确保有一个替代连接表的PK列。无论如何，（a_id，b_id）不能作为PK，因为这将不允许两者都为NULL。添加 UNIQUE 约束，它允许使用NULL值。

• Be sure to have a surrogate PK column for the connecting table. (a_id, b_id) cannot be the PK anyway, because that would disallow NULL in both. Add a UNIQUE constraint instead, which allows NULL values.

触发器针对性能进行了优化，只有在以下情况之一时才触发这两个FK列将被更新，并且仅当这两个结果都导致 NULL 时才被更新。

The trigger is optimized for performance and only kicks in when one of the two FK columns is updated, and only when that results in both being NULL.

触发器函数很简单：删除行并返回NULL以取消现在无效的级联 UPDATE 。

The trigger function is trivial: deletes the row and returns NULL to cancel the now void cascaded UPDATE.

这篇关于级联删除两个外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！