MySQL 5.7 外鍵約束無效

我有一個包含大約 150 個表的數據庫，以及它們之間的許多關係。

現在有時某些表的外鍵關係變得無效。因此，雖然設置了外鍵和索引，並且在更新/刪除時也設置為級聯；我得到無效的索引。

例如，表 A 具有作為外鍵關係中的值的行1。所以表 B 應該1作為它的主鍵。然而，它是另一個值，通常0（不為空）。

現在我不確定這什麼時候會發生，這就是我在這裡問的原因。我想設置一個外鍵，強制約束是有效的。但顯然它肯定有一些例外。

我認為可能是原因（我希望它是其中之一，如果這些問題可能在“正常”使用期間發生……我會害怕）：

• 重建/優化表
• 通過 HeidiSQL 或 phpMyAdmin 導出和導入數據庫

最大的問題是，數據庫看起來執行良好，因為大多數查詢都有效，只有少數插入/更新失敗。所以通常它會被忽視，直到出現一些錯誤。

實際問題：

• 為什麼會這樣？
• 我怎樣才能防止這種情況發生？
• 如果無法預防，“最佳實踐”解決方法是什麼？

shop_award_product.shop_product_id被引用shop_product.id。

好吧，shop_award_product.shop_product_id有0一段時間shop_product.id是不同的1或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
;

附加資訊：

MySQL 版本：5.7.15-0ubuntu0.16.04.1

根據評論，問題必須與導入和禁用外鍵檢查有關。但我不確定情況是否如此，因為我總是導出一個數據庫，然後在沒有實際修改 SQL 程式碼的情況下再次導入它。源數據庫顯然沒問題，而新數據庫有問題（我在幾週後發現）。

我很高興我沒有得到像“這種事情時常發生”這樣的回應。但仍然必須弄清楚它為什麼會發生，我想它必須是在導出和導入完成時，但我從不編輯導出。FOREIGN_KEY_CHECKS仍然是唯一被禁用的時刻。

導入期間不更改排序規則或字元集。我不是唯一能夠編輯表格的人，但其他人更改的機會非常小。在過去的 6 個月中，這種情況發生在我身上 4 次，那段時間我已經導入/導出了大約 100 db。這一次，所涉及的表格和所涉及的項目是恕我直言，其他人造成的可能性低於 1%。

我有解決你的一些問題

問）為什麼會發生這種情況？

A) 因為數據更改語句（例如 INSERT 或 UPDATE，…等）中的值無效或缺失

請注意禁用FOREIGN_KEY_CHECKS此違規的主要原因之一。

Q) 預防或解決方法 您可以向數據庫添加一些限制，我不確定這是否實用（DBA 應該能夠決定）

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

您還可以使用以下腳本https://github.com/michaelirey/mysql-foreign-key-checker檢查外鍵問題並修復它們。

關於重建/優化表，這可能會導致問題，我曾經在 SQL 伺服器上發生過。不確定MySQL。

我的不好，因為答案不夠組織，我會盡快重新組織。

引用自：https://dba.stackexchange.com/questions/153170