MySQL 5.7 foreign keys constraint invalid 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 &amp; 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.

And well, shop_award_product.shop_product_id had 0 while shop_product.id was different 1 or 3.

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
;


Additional information:
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 &amp; 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.
I have solution to some of your problems

Q) Why does this happen?

A) because invalid or missing values in data-change statements such as INSERT or UPDATE, ...etc

Note disabling FOREIGN_KEY_CHECKS one of the main causes of this violation.

Q) Preventing or workaround 
 There is some restriction   you can add to your database, I'm not sure if this practical (DBA should be able to decide)

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';


you can also use the following script https://github.com/michaelirey/mysql-foreign-key-checker to check foreign key problems and fix them.

Regarding rebuild / optimize table this may cause problem, it happened to me once on SQL server. not sure about MySQL.

my bad since the answer is not organized enough, I will try to re-organize it soon.