Re: Foreign key issue

[Dries Verschoren]

Below the tables and their setup:

CREATE TABLE `dadoge_module` (
`Module_Number` int NOT NULL,
`Module_Name` varchar(45) DEFAULT NULL,
`Module_Type_Name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Module_Number`),
UNIQUE KEY `idmodule_DADOGE_MODULES_UNIQUE` (`Module_Number`),
KEY `Module_Name_DADOGE_MODULE_INDEX` (`Module_Name`),
KEY `Tablemodule_Module_Type_Name_fk_idx` (`Module_Type_Name`),
CONSTRAINT `1Tablemodule_Module_Type_Name_fk` FOREIGN KEY (`Module_Type_Name`) REFERENCES `dadoge_moduletype` (`Module_Type_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_role` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Role_Module_Name` varchar(45) DEFAULT NULL,
`Role_Name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_Unique` (`Module_Name`,`Role_Name`),
KEY `Module_Role_Module_Name_index` (`Module_Name`,`Role_Module_Name`),
KEY `Module_Role_Module_Role_Name_index` (`Module_Name`,`Role_Module_Name`,`Role_Name`),
KEY `Module_Role_Name_index` (`Module_Name`,`Role_Name`),
KEY `Tablerole_Role_Name_fk_idx` (`Role_Module_Name`),
CONSTRAINT `1Tablerole_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `2Tablerole_Role_Name_fk` FOREIGN KEY (`Role_Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_controlstrategy` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Control_Strategy_Number` int DEFAULT NULL,
`Control_Strategy_Name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_Unique` (`Module_Name`,`Control_Strategy_Number`),
KEY `Module_Name_fk_idx` (`Module_Name`),
KEY `Module_Control_Strategy_Name_index` (`Module_Name`,`Control_Strategy_Name`),
CONSTRAINT `1Tablecontrolstrategy_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_setpoint` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Setpoint_Name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`Module_Name`,`Setpoint_Name`),
KEY `Module_Setpoint_Name_index` (`Module_Name`,`Setpoint_Name`),
CONSTRAINT `1Tablesetpoint_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_setpoint` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Setpoint_Name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`Module_Name`,`Setpoint_Name`),
KEY `Module_Setpoint_Name_index` (`Module_Name`,`Setpoint_Name`),
CONSTRAINT `1Tablesetpoint_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_role_cs_setpoint` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Role_Module_Name` varchar(45) DEFAULT NULL,
`Role_Name` varchar(45) DEFAULT NULL,
`Control_Strategy_Name` varchar(45) DEFAULT NULL,
`Setpoint_Name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Tablerolecssetpoint_Role_Module_Name_fk_idx` (`Module_Name`,`Role_Module_Name`,`Role_Name`),
KEY `Tablerolecssetpoint_Control_Strategy_Name_fk_idx` (`Role_Module_Name`,`Control_Strategy_Name`),
KEY `Tablerolecssetpoint_Setpoint_Name_fk_idx` (`Role_Module_Name`,`Setpoint_Name`),
KEY `Module_Role_Role_Control_Strategy_Name_index` (`Module_Name`,`Role_Name`,`Control_Strategy_Name`),
KEY `Module_Role_Role_Control_Strategy_Setpoint_Name_index` (`Module_Name`,`Role_Name`,`Control_Strategy_Name`,`Setpoint_Name`),
CONSTRAINT `1Tablerolecssetpoint_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_module` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `2Tablerolecssetpoint_Role_Module_Name_fk` FOREIGN KEY (`Module_Name`, `Role_Module_Name`) REFERENCES `dadoge_role` (`Module_Name`, `Role_Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `3Tablerolecssetpoint_Role_Name_fk` FOREIGN KEY (`Module_Name`, `Role_Module_Name`, `Role_Name`) REFERENCES `dadoge_role` (`Module_Name`, `Role_Module_Name`, `Role_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `4Tablerolecssetpoint_Control_Strategy_Name_fk` FOREIGN KEY (`Role_Module_Name`, `Control_Strategy_Name`) REFERENCES `dadoge_controlstrategy` (`Module_Name`, `Control_Strategy_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `5Tablerolecssetpoint_Setpoint_Name_fk` FOREIGN KEY (`Role_Module_Name`, `Setpoint_Name`) REFERENCES `dadoge_setpoint` (`Module_Name`, `Setpoint_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=389 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `dadoge_step_and_transitions` (
`id` int NOT NULL AUTO_INCREMENT,
`Module_Name` varchar(45) DEFAULT NULL,
`Sorting_Number` int DEFAULT NULL,
`Sequencer_Name` varchar(45) DEFAULT NULL,
`Step_Number` varchar(10) DEFAULT NULL,
`Step_Name` varchar(45) DEFAULT NULL,
`First_Text` varchar(255) DEFAULT NULL,
`Role_Name` varchar(45) DEFAULT NULL,
`Role_Control_Strategy` varchar(45) DEFAULT NULL,
`Role_Setpoint_Name` varchar(20) DEFAULT NULL,
`Step_Text` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `1Tablesat_Module_Name_fk_idx` (`Module_Name`),
KEY `2Tablesat_Role_Name_fk_idx` (`Module_Name`,`Role_Name`),
KEY `3Tablesat_Sequencer_Name_fk_idx` (`Module_Name`,`Sequencer_Name`),
KEY `4Tablesat_Control_Strategy_Name_fk_idx` (`Module_Name`,`Role_Name`,`Role_Control_Strategy`),
KEY `5Tablesat_Setpoint_Name_fk_idx` (`Module_Name`,`Role_Name`,`Role_Control_Strategy`,`Role_Setpoint_Name`),
CONSTRAINT `1Tablesat_Module_Name_fk` FOREIGN KEY (`Module_Name`) REFERENCES `dadoge_role` (`Module_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `2Tablesat_Role_Name_fk` FOREIGN KEY (`Module_Name`, `Role_Name`) REFERENCES `dadoge_role` (`Module_Name`, `Role_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `3Tablesat_Sequencer_Name_fk` FOREIGN KEY (`Module_Name`, `Sequencer_Name`) REFERENCES `dadoge_controlstrategy` (`Module_Name`, `Control_Strategy_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `4Tablesat_Control_Strategy_Name_fk` FOREIGN KEY (`Module_Name`, `Role_Name`, `Role_Control_Strategy`) REFERENCES `dadoge_role_cs_setpoint` (`Module_Name`, `Role_Name`, `Control_Strategy_Name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `5Tablesat_Setpoint_Name_fk` FOREIGN KEY (`Module_Name`, `Role_Name`, `Role_Control_Strategy`, `Role_Setpoint_Name`) REFERENCES `dadoge_role_cs_setpoint` (`Module_Name`, `Role_Name`, `Control_Strategy_Name`, `Setpoint_Name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci