I have two tables :
CREATE TABLE fiber ( fib_id uuid primary key default gen_random_uuid(), fib_number not null, fib_object uuid not null reference cable, --useless here but imagine a cable fib_optr uuid reference optical_road on delete set null, fib_color text not null ); -- optical road are multiple fiber, spliced together. -- Onces spliced, it is just one long fiber. CREATE TABLE optical_road ( optr_id uuid primary key default gen_random_uuid(), optr_name text not null, optr_client uuid reference client -- just an example );
I want to enforce the fact that you cannot delete a fiber if it is linked to an optical road. I cannot find a design without trigger.
Imagine this record :
fib_id | fib_number | fib_object | fib_optr | fib_color <anuuid> | 1 | <anuuid2> | <anuuid3>| #12345
Then here I want that
DELETE FROM fiber where fib_id=<anuuid> raises an error.