0

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);

Diagram

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.

  • Do you get the error when you try to an UPDATE? If yes, add the UPDATE statement. – ypercubeᵀᴹ Dec 5 '17 at 22:35
  • FOREIGN KEYs are sometimes a hassle. – Rick James Dec 6 '17 at 1:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.