0

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?

Tum
  • 3,614
  • 5
  • 38
  • 63

1 Answers1

1

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

StanislavL
  • 56,971
  • 9
  • 68
  • 98
  • the superVisorID of the supervisor is Null, didn't u see i showed in the question? – Tum Mar 13 '14 at 07:24
  • superVisorID of children (1 and 2) – StanislavL Mar 13 '14 at 07:25
  • u mean alter table "ON DELETE SET NULL"? – Tum Mar 13 '14 at 07:31
  • No, I mean UPDATE set supervisorID = NULL for the same records set call before DELETE – StanislavL Mar 13 '14 at 07:35
  • is that step is the same as "ON DELETE SET NULL" cos i tested "ON DELETE SET NULL" & it's working – Tum Mar 13 '14 at 07:37
  • ok, i got what u mean, but that like a hacking way, can "ON DELETE SET NULL" be more elegant? – Tum Mar 13 '14 at 07:38
  • may be you're reght and your approach is better. Implementation is up to you I just expressed the idea – StanislavL Mar 13 '14 at 07:39
  • u got 27K score so u got lot of experience, but why u say "Maybe"? is there any problem with "ON DELETE SET NULL" – Tum Mar 13 '14 at 07:42
  • I think ur approach is the same as "ON DELETE SET NULL" but u did it manually, while "ON DELETE SET NULL" do it automatically for you – Tum Mar 13 '14 at 07:45
  • 1
    @Tum: `ON DELETE SET NULL` would allow "illegal deletions", as you put it, in the same way as `ON DELETE CASCADE` would. Stanislav's approach, as I understand it, is to do the deletion in two explicit steps: first nullify the supervisors, then actually delete the rows. – Andriy M Mar 21 '14 at 10:17