0

Can anybody help, have one parent table, child table. child table is having foreign key constraint [ FOREIGN KEY(child_table_id) REFERENCES parent_table(primary_key) ON DELETE SET NULL ]. Using innodb engine. Some times even though parent key removes still foreign key exists. how that is possible in mysql ? When I try to update child table with fake value then not allowing to update and giving an sql constraint error. but why not marking as null on deletion of parent row. ?

Rajesh
  • 19
  • 1
  • 6
  • Can you supply a demo? I do not using ON DELETE SET NULL often, but just checked and everything works fine. When I deleted parent, child's FK has been set to NULL. – fifonik Feb 27 '19 at 03:29
  • public demo site: https://dbfiddle.uk/?rdbms=mysql_8.0 – danblack Feb 27 '19 at 03:41
  • The only way this should be possible (assuming your description is correct) is by disabling `FOREIGN_KEY_CHECKS` (see e.g. [here](https://stackoverflow.com/q/15501673)). Foreign keys are disabled in that mode, so you can mess with your data, and MySQL will not rechecked when you reenable it, just when you update the data the next time (which seems consistent with your description). Is it possible someone used that setting? (We cannot tell you when/how/who did it though; you might also check import scripts if you have those). – Solarflare Feb 27 '19 at 06:28
  • When that is on delete set null is not working, at that time if I create/update with fake value raising constraint error. only problem is when parent record deleted then child id automatically not set to null. – Rajesh Feb 27 '19 at 10:09
  • create table employee(eid int primary key auto_increment, ename varchar(40)) ; create table employee_det(emp_eid int default null, address varchar(50), CONSTRAINT `new_test_key` FOREIGN KEY(emp_eid) REFERENCES employee(eid) ON DELETE SET NULL) ; Once we created above two tables, for certain duration whenever we delete record from parent table child table column as null. After sometime, if we delete parent table information ,child table emp_eid column is still having value – Rajesh Feb 28 '19 at 05:12
  • Please try to add some concrete sample situation and make sure you are describing exactly what is going on. Specifically: at the time that setting null does not work anymore, make sure that (your assumption(?) that) inserting invalid rows actually produces an error; also check `select @@foreign_key_checks`; otherwise it may just be that after some time, someone set that mode. See [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fb439b444b9aa9d1c5c493d15ca9c23f) for the expected behaviour (change the setting from 0 to 1 and back). Try to reproduce your situation. – Solarflare Feb 28 '19 at 08:21
  • Can check output here https://pastebin.com/vVMZDpm7 when we tried to drop constraint and re-create it again it works. – Rajesh Feb 28 '19 at 12:17
  • Your example is completely consistent with the following situation: 1) you have a parent. 2) You add a child. 3) You disable foreign key checks. 4) You delete the parent -> it will NOT set anything to null, nor will it complain that your child has no parent. 5) You reenable foreign key checks. It will NOT complain that your child has no parent! (<- important to understand!) 6) You try to update the child (or add another child) -> the foreign key will complain. Play with the sample in my link to test this behaviour, and read the linked answer. If you are not convinced, maybe add another sample. – Solarflare Feb 28 '19 at 15:11
  • Thank you Solarflare, I agree that this scenario will be possible only when there is no foreign key checks, however I checked foreign key checks that still there. I tried to update child table with dummy value , it is throwing a constraint error. So only this trigger [ set to null on delete ] is not working. If we drop constraint and re-create it then everything is working perfect. – Rajesh Mar 01 '19 at 04:27
  • There is one bug in mysql 8 I think, https://bugs.mysql.com/bug.php?id=94400. Foreign key does not work if defined before referencing table. Thank you – Rajesh Mar 06 '19 at 05:26

0 Answers0