0

On MYSQL database version 5.5, I have tables with following foreign key constraint: CONSTRAINT fk_parent_child_id FOREIGN KEY (child_id ) REFERENCES parent (id ) ON DELETE NO ACTION ON UPDATE NO ACTION

It has been noticed that "child" table has some records that are not in "parent" table (and error 1451 starts to appear)

Note that this constraint is there since the creation of both tables Also, (SET FOREIGN_KEY_CHECKS = 0;) was not used while execution of any delete operations

Is there any clue for the cause of the inconsistency of data?

Thanks

Shymaa Ibrahim
  • 1
  • 2
  • 1
    "Is there any clue for the cause of the inconsistency of data?" Guess `SET FOREIGN_KEY_CHECKS = 0;` was used well inserting/updating or it isn't possible to insert/update records with a invalid reference – Raymond Nijland Mar 05 '18 at 14:48
  • 1
    Are the tables InnoDB or MyISAM? I ask because many MySQL developers think their table has a foreign key, but discover the table was created as MyISAM, which doesn't support foreign keys. Run `SHOW CREATE TABLE ` to double-check. – Bill Karwin Mar 05 '18 at 15:02
  • So far the cause of data inconsistency is not discovered and no script was gound that used "set foreign_key_checks = 0). Sometimes a database dump is imported and by default, the script disable foreign keys at the beginning to drop then recreate tables and their data, then at the end of the import script it enables foreign keys – Shymaa Ibrahim Mar 06 '18 at 20:22
  • Tables are InnoDB – Shymaa Ibrahim Mar 06 '18 at 20:24

0 Answers0