MySQL "ON DELETE CASCADE" is too powerful

Question

hey, following problem here: i have table with persons that have one location, if i delete the location for the location table, i don't wont to lose the assigned persons. ive a entry in the location with the id=1 and the name is: "No location". and that what I'm trying to do, but i fail!

ALTER TABLE `persons`
  ADD CONSTRAINT `persons_ibfk_1` FOREIGN KEY (`location_id`)
  REFERENCES `locations` (`id`) ON DELETE SET `location_id` = 1;

thanks!

Answer 1

Use ON DELETE SET DEFAULT (and make 1 the default value), or ON DELETE SET NULL. A third option is to manually set all ids to 1 before deleting.