one to many design struggle

Question

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.

score 2 · Answer 1 · answered Oct 13 '18 at 10:43

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

one to many design struggle

1 Answers1