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。
我的不好,因為答案不夠組織,我會盡快重新組織。