I have two tables one of which is for the polymorphic relationship of different corporations and I've added foreign key references to ids to ensure that if I delete a parent all children will be deleted. With this table setup below if I delete a parent corporation the child corporation persists which is not what I expected. If I delete a corporation_relationship via the parent_id the parent and its children cascade delete and if I a delete the relationship via the child_id the parent and siblings are unaffected. My questions are what am I doing wrong and how can I ensure that by deleting a parent the children are also deleted without adding any new columns?

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;

Example queries:

If I add 2 corporations and then add a relationship to the two like so:

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

My output for select * from corporations; will be:

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

My output for select * from corporation_relationships; is:

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

Now if I delete the 'father' by executing delete FROM corporations WHERE id = 'f9f8f7f6-f5f4-f3f2-f1f0-f0f0f0f0f0f0'; I would expect my output of select * from corporations; to be nothing but instead it is the following:

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

Also, it is noteworthy that the corporation_relationships table is empty after this delete as well but I would want the cascade to keep going past that table and delete the child entity as well.

CodePudding user response：

Your second foreign key constraint in the corporation_relationships table, that references to the corporations table has nothing with with your expectations of cascade deletions of children rows in corporations. To clearify, this foreign key do cascade deletions when you delete a referenced row in the corporations table. But you need the opposite.

To make it work as you expect in your design, you should have a column in corporations that references a primary key in corporation_relationships.

So you need to

1. create a primary key column, e.g. id, in corporation_relationships (not those you already have, it's not a pk, it's a unique constraint).
2. create a column in corporations and add a foreign key constraint on it that references a created corporation_relationships pk.
3. Remove a child_id column from corporation_relationships, it's incorrect and useless at this point.
4. When you create a relation you should set it's id to the fk column of corresponding child row in corporations. Now, if you delete a parent corporation, it would delete all relationships, those will delete corresponding children of corporation and so on recursively.

Meanwhile, in my opinion, your design is not correct.

To define a tree-like relations you do not need the transit table, i.e corporation_relationships. You can define it in a single corporations table. For that you need just a one column parent_id, those would be a foreign key with cascade delete rule, that references a pk in this table. Top-parent corporations would have a null in parent_id, all children - parent's id value.

Also, type column in corporation_relationships is not an attribute of relation itself, it's an attribute of child.

CodePudding user response：

Postgres doesn't mantain referential integrity with optional polymorphic relationships so I created a trigger to do this for me:

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