级联删除未预期工作的孩子

Question

我有两个表是针对不同公司的多态性关系，并且我添加了对ID的外键参考，以确保如果我删除父母，则所有孩子都将被删除。使用此表设置下面的设置，如果我删除父公司，孩子公司坚持不懈，这不是我所期望的。如果我通过父级及其子女级联删除corporation_realationship删除公司，如果我通过child_id删除关系，则父母和兄弟姐妹不受影响。我的问题是我做错了什么，如何通过删除父母而在不添加任何新专栏的情况下删除孩子？

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TYPE "corporation_relationship_type" AS ENUM (
  'campus',
  'network'
);

CREATE TABLE "corporations" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  "name" varchar(255) NOT NULL
);

CREATE TABLE "corporation_relationships" (
  "parent_id" uuid NOT NULL,
  "child_id" uuid NOT NULL,
  "type" corporation_relationship_type NOT NULL,
  PRIMARY KEY ("parent_id", "child_id")
);

ALTER TABLE "corporation_relationships" ADD FOREIGN KEY ("parent_id") REFERENCES "corporations" ("id") ON DELETE CASCADE;

ALTER TABLE "corporation_relationships" ADD FOREIGN KEY ("child_id") REFERENCES "corporations" ("id") ON DELETE CASCADE;

示例查询：

如果我添加了2家公司，然后将关系添加到类似的两个公司：

insert into corporations (id, name) values ('f9f8f7f6-f5f4f3f2-f1f0f0f0-f0f0f0f0', 'Father');


insert into corporations (id, name) values ('f9f8f7f6-f5f4f3f2-f1f0f0f0-f0f0f0f1', 'Son');

insert into corporation_relationships (parent_id, child_id) values ('f9f8f7f6-f5f4f3f2-f1f0f0f0-f0f0f0f0', 'f9f8f7f6-f5f4f3f2-f1f0f0f0-f0f0f0f1');

我的select *从公司中选择 *的输出；将是：

                  id                  |        name
--------------------------------------+--------------------
 f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f0 | Father
 f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f1 | Son
(2 rows)

我的select * select * from corporation_relationships; IS：

              parent_id               |               child_id               |  type
--------------------------------------+--------------------------------------+--------
 f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f0 | f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f1 | campus

现在，如果我通过执行从id ='f9f8f7f6-f5f4-f4-f3f2-f1-f0-f0-f0-f0-f0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f0'; 删除“父”;，我希望我的输出我的输出从公司; 什么都不是，但相反，它是以下内容：

                  id                  |        name
--------------------------------------+--------------------
 f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f1 | Son
(1 row)

还值得注意的是，Corporation_Relationships表格在此删除后也为空，但是我希望Cascade继续越过该表格并删除儿童实体并删除该表格也是如此。

Answer 1

您在corporation_realationships表中的第二个外键约束，引用corporations表不带您对corporiations 表。 >。要澄清，此外键在Corporations表中删除引用的行时，请级联删除。但是你需要相反的。

为了使其正如您在设计中所期望的所期望的那样，您应该拥有 corporations中的一列，该专栏引用了corporation_relationships < /strong>。

因此，您需要

1. 创建一个主键列，例如 id ， corporation_relationships （不是您已经拥有的那些，这不是PK，它是独特的约束）。
2. 在Corporations中创建列 和添加外键对其参考创建corporation_ryationships pk。
3. 从Corporation_Relationationships 中删除child_id列，此时它是不正确且无用的。
4. 当您创建一个关系时，应该将其设置为 id fk列 conder> comporiations 中的相应 Child Row 。
   现在，如果您删除a parent Corporation ，它将删除所有关系，这些将递归删除Corporation 的子女。

同时，我认为您的设计不正确。

要定义类似树的关系，您不需要过境表，即
Corporation_Realationships。您可以在单个公司表中定义它。为此，您只需要一个列 parent_id ，这些将是带有级联删除规则的外键，在此表中引用a pk 。顶级父母公司将在 parent_id 中具有 null ，所有孩子 - 父母的 id value。

另外， type corporation_realationships不是关系本身的属性，而是儿童的属性。

Answer 2

Postgres与可选的多态关系没有晶体的参考完整性，因此我为我创建了一个触发因素：

CREATE FUNCTION cascade_delete_children() RETURNS trigger AS $
    BEGIN
        -- Check if the corporation is a parent
        IF OLD.id IN (SELECT parent_id FROM corporation_relationships) THEN
            -- Delete all of the corporation's children
            DELETE FROM corporations WHERE id IN (SELECT child_id FROM corporation_relationships WHERE parent_id = OLD.id);
        END IF;
        RETURN OLD;
    END;
$ LANGUAGE plpgsql;

CREATE trigger cascade_delete_children BEFORE DELETE ON corporations
    FOR EACH ROW EXECUTE PROCEDURE cascade_delete_children();