CODEWITHSUNDEEP
mysqlsqldatabase
Tum
Tum

Reputation: 3652

is it good practice to remove the foreign key then delete records & finally set the foreign key back?

I have this problem. I got this following table:

employeeID - supervisorID - workerType
1          - 3            - 1
2          - 3            - 1
3          - NULL         - 1

supervisorID is actually the foreign of the primary key "employeeID". Each employee belongs to a workerType.

Now i want to delete all the workerType=1, so it will delete the employeeID=1, employeeID=2 & employeeID=3. However mysql won't allow cos it got the foreign key constraints (ie employeeID=3).

Also, i don't want on delete cascade cos I want to reject the illegal deletion (that is only manager can delete employee). If i allow on delete cascade then i have to build another system to control illegal deletion but that cos more energy.

I am using old mysql 5.0

is it good practice to remove the foreign key then delete records & finally set the foreign key back?

So how to delete workerType=1 without removing the foreign key?

Upvotes: 0

Views: 261

Answers (1)

StanislavL
StanislavL

Reputation: 57421

You can set FK values to NULL (supervisorID column if I understand correctly) and then delete.

Upvotes: 1

Related Questions