无法删除或更新父行，外键约束失败[关闭]

Question

I'm getting an error saying "Cannot delete or update a parent row, foreign key constraint fails." when I'm trying to update the values in the team table. What am I doing wrong?

$createTeam ="CREATE TABLE Team(
    teamName VARCHAR(30) not null,
    division VARCHAR(30) not null,
    photo VARCHAR(30),
    primary key(teamName, division)
    )
    engine=innodb";


$createParticipant ="CREATE TABLE Participant(
    participantName VARCHAR(30) not null,
    techniqueResult DOUBLE(10,2),
    trickResult INT(10),
    teamName VARCHAR(30) not null,
    division VARCHAR(30) not null,
    primary key (participantName),
    foreign key (teamName, division) references Team(teamName, division)
    ON DELETE CASCADE
    )
    engine=innodb";




$updateTeam = "UPDATE Team SET teamName = '$newTeamName' , division = '$newDivision' WHERE teamName = '$oldTeamName' AND division ='$oldDivision'";

Answer 1

You need to re-think your schema.

Team and Division seems like they should be two separate entities with their own IDs.

Now depending on your business model, if a Participant can only be in one Team and one Division then you simply add two foreign keys in Participant to reference Team and Division respectively.

If a Participant can be in more that one Teams and/or Divisions you need to introduce a conceptual Entity DivisionTeamParticipant that will contain multiple rows of foreign keys for Team, Division and Participant.

EDIT @paulF

If your hierarchy is strict than it should be: Division -> Team -> Participant. So you would only need a foreign key in Team to reference Division and one in Participant to reference Team.