I have a database with around 150 tables, and many relations between them.
Now sometimes the foreign key relation of some tables becomes invalid. So while the foreign keys and indexes are set, and also set to cascade on update/delete; I get invalid indexes.
As example Table A has as row with a value in the foreign key relation
1. So Table B should have
1 as its primary key. Yet it's another value, usually
0 (not null).
Now I am not sure when this does happen, that is why I am asking here. I thought setting a foreign key, enforces the constraint to be valid. But apparently there must be some exception to it.
Things that I suppose could be the cause (I hope it's one of those, if these problems can happen during "normal" usage .. I will be scared):
- rebuild / optimize table
- export & import of the database through HeidiSQL or phpMyAdmin
The biggest problem with this is, that the database looks like it's running fine, as most queries work, only a few inserts/updates fail. So usually it gets by unnoticed, till some bug arises.
The actual questions:
- Why does this happen?
- How can I prevent this from happening?
- If it can't be prevented, what are the "best practice" workarounds?
shop_award_product.shop_product_id is referenced with
shop_product.id was different
CREATE TABLE `shop_award_product` ( `shop_award_id` INT(11) UNSIGNED NOT NULL, `shop_product_id` INT(11) UNSIGNED NOT NULL, `sort_order` INT(11) UNSIGNED NOT NULL DEFAULT '1000', `created_at` DATETIME NOT NULL, `created_by` INT(11) UNSIGNED NOT NULL, `updated_at` DATETIME NOT NULL, `updated_by` INT(11) UNSIGNED NOT NULL, PRIMARY KEY (`shop_award_id`, `shop_product_id`), INDEX `shop_product_id` (`shop_product_id`), CONSTRAINT `fk_shop_awardproduct$shop_product` FOREIGN KEY (`shop_product_id`) REFERENCES `shop_product` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; CREATE TABLE `shop_award` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `website_id` INT(11) UNSIGNED NOT NULL, `created_at` DATETIME NOT NULL, `created_by` INT(11) UNSIGNED NOT NULL, `updated_at` DATETIME NOT NULL, `updated_by` INT(11) UNSIGNED NOT NULL, PRIMARY KEY (`id`), ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=4 ;
MySQL version: 5.7.15-0ubuntu0.16.04.1
According to a comment, the problem has to be with import and disabling the check for foreign keys. But I am not sure yet that is the case, as I have always exported a db, and imported it again without actually modifying the SQL code. And the source DB is apparently ok, while the new DB has the problem (which I discover weeks later).
I am glad I got no response like "this stuff just happens from time to time".
But still have to figure out why it happens, I suppose it has to be when an export & import is being done, but I never edit the export. Still it's the only moment when
FOREIGN_KEY_CHECKS is disabled.
No change of collation or character set during the import. I am not the only person able to edit the tables, but the chance that someone else changed that is very small. It has happened to me like 4 times in the past 6 months, and I have imported/exported about 100 db's in that time. This time, the tables involved and the project involved it is IMHO below 1% chance that someone else caused it.