Mysql

MySQL 5.7 外鍵約束無效

  • November 2, 2016

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

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

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

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

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

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

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

實際問題:

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

shop_award_product.shop_product_id被引用shop_product.id

好吧,shop_award_product.shop_product_id0一段時間shop_product.id是不同的13

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