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?

Answer 1

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

Answer 2

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)