one to many design struggle 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 
 &lt;anuuid&gt; |          1 | &lt;anuuid2&gt;  | &lt;anuuid3&gt;| #12345   


Then here I want that DELETE FROM fiber where fib_id=&lt;anuuid&gt; raises an error.
optical_road {OPTR_ID, OPTR_NAME, OPTR_CLIENT}
          PK {OPTR_ID}


fiber {FIB_ID, FIB_NUMBER, FIB_OBJECT, FIB_COLOR}
   PK {FIB_ID}



fiber_road {FIB_ID, OPTR_ID}
        PK {FIB_ID}

       FK1 {FIB_ID}  REFERENCES fiber {FIB_ID} 
                     ON DELETE RESTRICT

       FK2 {OPTR_ID} REFERENCES optical_road {OPTR_ID} 
                     ON DELETE RESTRICT


Note:


PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY