SQL - deleting rows from other tables with on cascade not working

Question

I have 4 related tables: order_info, master_bill, master_bill_order_leg and order_leg.

MasterBill and OrderLeg have many to many relationship.

This is the quick documentation from IntelliJ for the DB tables.

Table master_bill:

create table master_bill
(
    id                    bigint                                 not null
        primary key,
    mb_no                 bigint                                 not null,
    created_by            text                                   not null,
    updated_by            text                                   not null,
    created_at            timestamp with time zone default now() not null,
    updated_at            timestamp with time zone default now() not null
);

Table order_info:

create table order_info
(
    order_id                  bigint                                        not null
        primary key
        references public.order_t
            on delete cascade,
    mb_id                     bigint                                        not null
        references master_air_waybill
            on delete cascade,
    created_by                text                                          not null,
    updated_by                text                                          not null,
    created_at                timestamp with time zone default now()        not null,
    updated_at                timestamp with time zone default now()        not null
);

Table master_bill_order_leg:

create table master_bill_order_leg
(
    mb_id               bigint
        references master_bill
            on delete cascade,
    order_leg_id        bigint
        references order_leg
            on delete cascade,
    constraint master_bill_order_mb_id_order_leg_id_key
        unique (mb_id, order_leg_id)
);

Table order_leg:

create table order_leg
(
    id             bigserial
        primary key,
    created_by     text                                   not null,
    updated_by     text                                   not null,
    created_at     timestamp with time zone default now() not null,
    updated_at     timestamp with time zone default now() not null,
    constraint order_leg_unique_c
        unique (flight_id, flight_date, departure_iata, arrival_iata)
);

I have set the foreign keys and they look like this:

Table master_bill_order_leg

master_bill_order_leg_mb_id_fkey (mb_id) -> master_bill(id)
master_bill_order_leg_order_leg_id_fkey (order_leg_id) -> order_leg(id)

Table order_info

order_info_mb_id_fkey (mb_id) -> master_bill(id) ON DELETE CASCADE
order_info_order_id_fkey (order_id) -> order_t(id) ON DELETE CASCADE

I thought that if order from order_t table is deleted that all relevant rows from other tables would be deleted too, but that is not the case, only order_info row is deleted on cascade. So, I have tried with deleting a row from master_bill table:

DELETE
FROM master_bill
WHERE id = :mbId

But, then I get an error:

org.postgresql.util.PSQLException: ERROR: update or delete on table "master_bill" violates foreign key constraint "master_bill_order_leg_mb_id_fkey" on table "master_bill_order_leg" Detail: Key (id)=(1076) is still referenced from table "master_bill_order_leg".

I thought that this foreign key would delete on cascade from master_bill_order_leg table if I delete a row from master_bill, since master_bill is a parent table. What am I doing wrong here?

What happens with ALTER TABLE order_info DROP constraint (order_info_mb_id_fkey) ALTER TABLE order_info ADD foreign key (mb_id) REFERENCES master_bill(id) ON DELETE CASCADE — SE1986, Mar 04 '22 at 00:06
I have updated question, I have managed to get migration working, but ON DELETE CASCADE is not working for some reason rows are not deleted from other tables. — Ludwig, Mar 04 '22 at 00:08
The parentheses around constraint names are plain syntax errors. — Erwin Brandstetter, Mar 04 '22 at 06:46
Please post table structure as we can help you to create foreign key. For now you have no foreign key references order_info. So if you delete order_info nothing else wil bedeleted. But if you delete master_bill, the children in order_info will be deleted to because of foregn key `ALTER TABLE order_info ADD foreign key (mb_id) REFERENCES master_bill(id) ON DELETE CASCADE`. Your foreign keys not written as it should be. — Philippe, Mar 04 '22 at 10:42
I have updated question @Philippe, will try to post DB structure as well. — Ludwig, Mar 04 '22 at 14:06
This question has now changed twice - what are you actually trying to achieve? — SE1986, Mar 04 '22 at 16:33
Nothing can prevent a record to be deleted without error except a trigger with Rollback command. — Meyssam Toluie, Mar 06 '22 at 05:51
The table definition says `mb_id ... references master_air_waybill`. Below you say `order_info_mb_id_fkey (mb_id) -> master_bill(id)`. One table is schema-qualified (`public.order_t`), others not. Are there any other schemas in use than `public`? Please present a consistent question. Also, the **only** explanation that your `DELETE` neither deletes rows nor throws an exception is that the given IDs do not exist in the table. There must be a misunderstanding *somewhere*. — Erwin Brandstetter, Mar 06 '22 at 23:24
Accidentally targeting a **table of the same name in a different schema** would explain it. Are you aware of schemas and the `search_path` in Postgres? See: https://stackoverflow.com/a/9067777/939860 — Erwin Brandstetter, Mar 06 '22 at 23:27

Philippe · Accepted Answer · 2022-03-08T11:08:00.503

You can use both foreign key and trigger to achieve this. (Result here)

create table master_bill
(
    id bigint not null primary key,
    mb_no bigint not null,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);


create table order_info
(
    order_id bigint not null primary key,
    mb_id bigint not null references master_bill(id) on delete cascade,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);

create table master_bill_order_leg
(
    mb_id bigint references master_bill(id) on delete cascade,
    order_leg_id bigint,
    constraint master_bill_order_mb_id_order_leg_id_key unique (mb_id, order_leg_id)
);

create table order_leg
(
    id bigint primary key,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);

CREATE OR REPLACE FUNCTION public.f_delete_order_leg()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    delete from order_leg where id in (select order_leg_id from old_table);
    
    return null;    
end;
$function$
;

create trigger t_delete_order_leg 
after delete
on master_bill_order_leg 
referencing old table as old_table
for each statement 
execute function f_delete_order_leg();

Unfortunately, I can't change table since it is a legacy code I am working with and I have existing data already in DB — Ludwig, Mar 04 '22 at 15:03
how would you write a trigger for this? P.S. it is a many to many relationship between ```master_bill``` and ```order_leg``` — Ludwig, Mar 07 '22 at 12:38
What are the deletion rules ? Can i delete an `order_leg` without deleting a `master_bill` ? ... — Philippe, Mar 07 '22 at 16:31

SE1986 · Answer 2 · 2022-03-04T00:33:42.973

The way a CASCADE DELETE works is when you delete a record in the parent table, the related records in the child table are deleted

When you are running

DELETE
FROM order_info
WHERE order_id = :id

this is a child table (its FK references a PK in a parent table) it doesn't have any foreign keys in other tables that reference it

A simple example of how cascade deletes works is a two table database of departments and employees - one department can have many employees and this is enforced by an FK / PK relationship:

CREATE TABLE Department
(
    DeptId INT PRIMARY KEY,
    DeptName VARCHAR(10)
);

CREATE TABLE Employee 
(
    EmployeeID INT ,
    EmployeeName VARCHAR(10),
    DepartmentID INT,
    CONSTRAINT DepartmentID_FK foreign key (DepartmentID) references Department(DeptId) ON DELETE CASCADE
);

INSERT INTO Department VALUES (1,'IT'),(2,'HR');

INSERT INTO Employee VALUES (1,'John',2),(2,'Kim',2),(3,'Sam',1);

At this point, if I run

SELECT * FROM Employee

I get 3 records.

If I delete a department from the parent table:

DELETE FROM Department WHERE DeptID = 2

and I run the select again, I now have 1 record (Sam) as the cascade delete has deleted those employees in department ID 2 (john and Kim)

Thank you for your explanation I have tried to use that, but unfortunately for some reason when I try to delete rows now nothing is being deleted. I have updated my question with the new situation, I am wondering if some constraints are now blocking deletion of rows? — Ludwig, Mar 04 '22 at 14:05

SQL - deleting rows from other tables with on cascade not working

2 Answers2

Linked