I am having an issue with a circular dependency. Inside my "Team Engineers" table, there is the name of the Engineer and also a Boss. A boss is also an Engineer as well. Whenever I want to update a Boss, I want to update to the whole entire team of engineers that are associated to that boss, but I keep getting a 1451 error every time. I have attached my DDL as well as a diagram.
create table `Team Engineers` ( Engineer_Name char(50) not null, Team char(50) not null, Role char(100) not null, Boss char(50) null, primary key (Engineer_Name, Team), constraint Team Engineers_Teams_Team_Name_fk foreign key (Team) references Teams (Team_Name) on update cascade, constraint Team Engineers_Team Engineers_Engineer_Name_fk foreign key (Boss) references `Team Engineers` (Engineer_Name) on update cascade ); create index `Team Engineers_Teams_Team_Name_fk` on `Team Engineers` (Team); create index `Team Engineers_Team Engineers_Engineer_Name_fk` on `Team Engineers` (Boss);
Specific Error Message:
" Cannot delete or update a parent row: a foreign key constraint fails (`F1_db`.`Team Engineers`, CONSTRAINT `Team Engineers_Team Engineers_Engineer_Name_fk` FOREIGN KEY (`Boss`) REFERENCES `Team Engineers` (`Engineer_Name`) ON UPDATE CASCADE)"
I've seen where people say that this error is common because the MySQL DB can't multiple operations at the same time.