Postgresql - delete on cascade not deleting from a child table

Question

I have tables master_bill and 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 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)

I thought that if I delete a row from the parent master_bill table that all relevant rows from the child table master_bill_order_leg would be deleted too because of foreign keys that are set with on delete 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".

Why do I get this error, what am I doing wrong here?

If I run this query directly from the query console then a row is deleted from the table master_bill and a child table master_bill_order_leg.

Hmm. My steps to debug would be to use `psql` to 1) Confirm the table definitions are what you show. 2) Run the `DELETE FROM master_bill WHERE id = ` to take JDBC out of the mix. I would also look at the Postgres log to see if there is an error that you are not seeing from the JDBC output. — Adrian Klaver, Mar 07 '22 at 16:19
When I run the ```DELETE FROM master_bill WHERE id = ``` from console, a row is deleted from the table. What would be the command for the number 1. you mentioned, to show table definitions, I only used intellij to do that so far? — Ludwig, Mar 07 '22 at 16:22
`psql` commands can be found in the program by doing `\?` with more explanations found in the docs at [psql](https://www.postgresql.org/docs/current/app-psql.html). For this case you want `\d ` — Adrian Klaver, Mar 07 '22 at 16:27
Are you using both References and Foreign key syntax? If so you probably have two sets of foreign keys. — Bjarni Ragnarsson, Mar 07 '22 at 16:35
@BjarniRagnarsson I can only see 2 keys for the table ```master_bill_order_leg``` on inspecting in Intellij. — Ludwig, Mar 07 '22 at 16:39
I have updated my answer in this post to use both foreign key and trigger with result in dbfiddle [https://stackoverflow.com/questions/70290842/sql-deleting-rows-from-other-tables-with-on-cascade-not-working](https://stackoverflow.com/questions/70290842/sql-deleting-rows-from-other-tables-with-on-cascade-not-working) — Philippe, Mar 08 '22 at 13:56

Postgresql - delete on cascade not deleting from a child table

0 Answers0