Good day. There are tables of departments and users . Records from the users table may have a link to records from the departments , but not necessarily, in the definition of the column it is worth that it may be NULL .

It was necessary to establish reference integrity on them, that is, when deleting a record from the departments , if someone in users has a link to it, then an error should be sent. I use this request to install a foreign key:

 ALTER TABLE `users` ADD CONSTRAINT `users_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE RESTRICT;

And the whole thing is that it does not work for columns that can be NULL . When deleting an entry from the departments , in the users table, the value of the department_id column is stupidly set to NULL . If you disable NULL , then of course everything works and the error will fly out. But it is necessary for me that the column could be NULL . Tell me what to do. The engine, if anything, InnoDB , just read that it only works on it.

  • You can create a branch "No branch" =) - vp_arth
  • NOT NULL is required, and this is not discussed. Therefore, you must use the solution that vp_arth offers. - Akina
  • one
    The variant with NOT NULL is much preferable (there is no need to reinvent the wheel, use indexes more efficiently, simplifies queries ...). However, if NULL should be allowed without fail, a trigger should be created that, when attempting to delete a record, will perform the necessary checks. Although it is a crutch. - Aleksei
  • @Aleksei, Okay, I understood everything, thanks. - Klym

0