The CATEGORIES table:

CREATE TABLE `CATEGORIES` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `NAME` char(30) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The SUBCATEGORIES table:

CREATE TABLE `SUBCATEGORIES` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `CATEGORY_ID` int(11) DEFAULT NULL,
 `TYPE` tinyint(4) NOT NULL,
 `NAME` char(30) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`ID`),
 KEY `CATEGORY_ID` (`CATEGORY_ID`),
 CONSTRAINT `SUBCATEGORIES_ibfk_1` FOREIGN KEY (`CATEGORY_ID`) REFERENCES `CATEGORIES` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What do I want?

I want to know if is it possible to switch between CASCADE and SET NULL according to the TYPE value.

Here is what must happen when deleting the category:

• If the type of the subcategory was 45 then delete the subcategory.

• If the type of the subcategory was 63 then set a null value inside CATEGORY_ID.

What is the best way to do that?

CodePudding user response：

As far as I know there is no direct way to dynamically alter between CASCADE and SET NULL from the constraint's perspective. Though I'm unsure of this being the best way, we can certainly use a trigger to enforce the rule dynamically. Before creating the trigger, make sure we set ON DELETE to SET NULL for the FK.

delimiter //
DROP TRIGGER IF EXISTS  before_delete//
create trigger before_delete before delete on CATEGORIES for each row
begin
if exists (select 1 from SUBCATEGORIES where CATEGORY_ID=old.ID and type=45) then
delete from SUBCATEGORIES where CATEGORY_ID=old.ID and type=45;
end if;
end//

delimiter ;
delete from CATEGORIES where ID=5;
delete from CATEGORIES where ID=7;

select * from SUBCATEGORIES;
-- result set (the NAME column removed)
# ID, CATEGORY_ID, TYPE
5, , 63
6, 18, 63
13, 18, 45
15, 18, 63
21, , 63
26, 18, 45