Circular Dependency Issue Error 1451

Question

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:

"[23000][1451] 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.

Answer 1

You haven't provided enough details but the problem is likely caused by the ill-defined foreign key. You have a foreign key (1 column) that does not reference the primary key (which is 2 columns):

primary key (Engineer_Name, Team),

foreign key (Boss) references `Team Engineers` (Engineer_Name)

This construction - while it is allowed by MySQL - it is not recommended, even by its documentation.

Solution:

Adjust the foreign key reference so it matches the primary key. I see 2 options here, depending on the business requirements. Can the Boss of an Engineer be on a different Team? Or are they always on the same Team?

If they can be on separate teams, then you need a Boss_Team column, too. Code:

-- added
Boss_Team char(50) null,           

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Boss_Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

If Engineer and Boss are always on the same Team:

-- edited
constraint Team_Engineers_Boss_fk
    foreign key (Boss, Team)
        references Team_Engineers (Engineer_Name, Team)
        on update cascade

I assume that the PRIMARY KEY cannot be modified. If it is changed (say Engineer_Name becomes the primary key), then the foreign key you have will work correctly.