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 & 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

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.

StackExchange Database Administrators Q#153170, answer score: 2