Search code examples
mysqlsqljoinphpmyadminsql-delete

Delete other data with foreign key

I am making a application for my internship. We play games in the break, and I am making a application to keep track of the score.

I can add games with players. After that, I can edit the game and assign a winner. The games table & winners table are both seperate.

Players: ID(PK), name
Games: ID(PK), player_1(FK), player_2(FK) --- Connected to players.id
Winners: ID(FK), name (FK) --- winners.id connected to games.id, name to players.

If I haven't assigned a winner in the game, I can safily delete a record. However, if I assigned a winner, I can't: Cannot delete or update a parent row: a foreign key constraint fails

I know why, because the winner is connected with a game. But how can I remove a game and immediatly delete the winner from the other table?

Solution

  • First delete the foreign key constraint from winners:

    ALTER TABLE winners DROP FOREIGN KEY yourforeignkeyname

    Then recreate it with ON DELETE CASCADE:

    ALTER TABLE winners 
ADD CONSTRAINT yourforeignkeyname 
FOREIGN KEY (ID) 
REFERENCES games(ID) 
ON DELETE CASCADE;

    Now when you delete a game, the winner rows will also be deleted.