Trigger before uninstalling MySql

Question

I have a table called user. This table has a foreign key for the department table. One user can be associated with one department. Before deleting a department, I would like to set the default value (1) for any user (having this department identifier) ​​to avoid referential integrity errors.

Do you know a good example. Most examples show that a trigger applies to a single table. Here the trigger should be launched on the department, but change the values ​​in the user table.

Thank.

Answer 1

I have not tested it, but based on the documentation , this looks right:

CREATE TRIGGER update_user_before_delete BEFORE DELETE ON department
  FOR EACH ROW BEGIN
    UPDATE user SET department = 1 WHERE department = OLD.department;
  END;

Answer 2

NULL, , , 1, .

, , .

- :

ALTER TABLE `user`
ADD CONSTRAINT FK_USER_TO_DEPARTMENT FOREIGN KEY (department_id) 
REFERENCES `department` (department_id) ON DELETE SET NULL;

Answer 3

sql . , -. , ( ), . .