Problem with foreign key constraints and construction of Mysql DB

Question

I'm having a problem with thinking of the way to connect two tables. I have one table with actions (RAD):

CREATE TABLE RAD (
  rad_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  spec_id INT NULL,
  predp_id INT NULL,
  predf_id INT NULL,
  strp_ID INT NULL,
  strf_ID INT NULL,
---more fileds---
  FOREIGN KEY (spec_id) REFERENCES SPEC(spec_id) ON DELETE SET NULL,    
  FOREIGN KEY (strp_ID) REFERENCES STRANKEP(strp_ID) ON DELETE CASCADE,
  FOREIGN KEY (strf_ID) REFERENCES STRANKEF(strf_ID) ON DELETE CASCADE,
  FOREIGN KEY (predp_id) REFERENCES PREDMETIP(predp_id) ON DELETE CASCADE,
  FOREIGN KEY (predf_id) REFERENCES PREDMETIF(predf_id) ON DELETE CASCADE
) ENGINE=InnoDB COLLATE utf8_general_ci;

And one table of specifications (SPEC) based on whom bill will be made:

CREATE TABLE SPEC (
  spec_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ---more fileds---    
) ENGINE=InnoDB COLLATE utf8_general_ci;

As you can see action rad_id(RAD) row will be deleted if any client (strp_ID or strf_ID) will be deleted. The same goes for case(predp_id and predf_id).

Now I want to restrict delete of action rad_id(RAD row) if its included in specification. Therefore, when specification is made it inserts spec_id(SPEC) in spec-id(RAD) filed.

When specification is deleted field goes back to null and that works. BUT it allows me to delete the action rad_id(RAD) when it was included in specification(SPEC) and has that foreign key spec_id included in RAD table. And I can not let that happen. It should delete only when its null and specification key is not present.

The problem is specification will contain MULTIPLE actions rad_id's(RAD) so I can not tie it with one more column rad_id(RAD) as foreign key.

I don't know how to approach this problem.

     RAD TABLE  
rad_id  spec_id
1       1
2       1
3       1
4       null

        SPEC TABLE  
spec_id     rad_id-reference
1           1,2,3

As seen above SPEC table row will be made out of 3 rad_id's, I need a way to say rad_id's 1,2 and 3 can not be deleted if spec_id 1 exists. rad_id 4 can be deleted.

The problem is that I can not make rad_id-reference on SPEC table a FOREIGN KEY made out of 3 rad_id's.

I deleted my answer since the question is not clear to me anymore. Please create a running (and simple) example in SQL Fiddle, so I can help you better. — The Impaler, Mar 15 '20 at 20:07
http://sqlfiddle.com/#!9/50de2/1 Messy, but works, if you have another suggestion id appreciate. — ikiK, Mar 16 '20 at 03:40
I think I get the problem now. My take is that the fool proof solution is to create a pre-delete trigger (`BEFORE DELETE`) on the RAD table that verifies that "spec_id is null" before deleting the row. If it's not null, then the trigger will prevent the deletion. Is that what you need? See https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html — The Impaler, Mar 16 '20 at 13:18
Yeah that would be great but Cascaded foreign key actions do not activate triggers :) . https://stackoverflow.com/questions/6041064/trigger-calls-in-cascade-deleting . And I have turned on cascading all over the db before knowing this. But yes you are right that would work in idle situations. Thank you for your input. — ikiK, Mar 16 '20 at 13:52

score 0 · Accepted Answer · answered Mar 16 '20 at 03:39

I have found a way to do this. http://sqlfiddle.com/#!9/50de2/1 If you change a delete value into 1 it will fail.

     RAD TABLE  
rad_id-PK
1       
2       
3       
4       

        SPEC TABLE  
spec_id-PK
1
2

    RESTRICTDEL TABLE
res_id-PK  spec_id-FK  rad_id-FK
1           1           1
2           1           2
3           2           2
4           2           3
5           2           3

I have made another table that will contain both PK id's in one column, and they are FK's. one is PK and it will be unique just like spec_id From SPEC table. second is rad_id that can be double. I just have to set SET foreign_key_checks = 0; and back 1 when I'm done with inserting a new specification. Also will need to loop with php and for every rad_id make new resdel_id entry. This way multiple rad_id wont be deleted if there is one spec_id connecting them.

Problem with foreign key constraints and construction of Mysql DB

1 Answers1