0

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.

  • 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> raise an error – Rémi Desgrange Oct 12 '18 at 14:40
  • This doesn't make a lot of sense to me. If I understand you correctly, you want to prevent a child record (fiber) from being deleted if it has a parent (optical_road). Since the foreign key is not nullable, then by definition all records in fiber have parents in optical_road, so none can be deleted according to your logic, which can only be prevented by not granting the delete privilege. – mustaccio Oct 12 '18 at 15:18
  • 1
    Read this post it appears to be a similar requirement. dba.stackexchange.com/questions/127107/… – George Oct 12 '18 at 15:59
2
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
|improve this answer|||||
  • OK, so you're implementation looks good to expect that the PK of fiber_road will be FIB_ID, OPTR_ID and fib_id will be unique. – Rémi Desgrange Nov 4 '18 at 14:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.