MySQL Cannot Add Foreign Key Constraint 錯誤如何修復？

已發表: 2022-12-22

如何修復“ MySQL 無法添加外鍵約束”錯誤？ 嘗試向 MySQL 表添加外鍵約束時，您可能會收到以下錯誤消息：“ ERROR 1215 (HY000): Cannot add foreign key constraint ”。

像這樣的錯誤非常普遍，並沒有提供有關 MySQL 無法將外鍵約束插入表的具體原因的具體細節。要解決此問題，您需要確定問題的確切原因。

本文將通過四種行之有效的方法解釋MySQL出現“#1215 – Cannot Add Foreign Key Constraint”錯誤的原因，如何查找原因，以及如何修復。

MySQL Cannot Add Foreign Key Constraint 錯誤如何修復？

MySQL報“#1215 – Cannot Add Foreign Key Constraint”錯誤是什麼原因？

此錯誤可能有多種原因。為了確定此錯誤的原因，最好檢查 SHOW ENGINE INNODB STATUS 的 LATEST FOREIGN KEY ERROR 部分。

這樣，您將對問題有更好的理解。您將能夠從以下列表中識別您的案例：

通常，在加載轉儲時，約束所指的表或索引不存在

有時，客戶希望添加約束的數據庫表不存在，導致出現 1215 錯誤信息。

但是，沒有跡象表明該表不存在，因此建議您使用該命令來確定數據庫中有哪些表可用。

在每個父表上運行 SHOW TABLES 或 SHOW CREATE TABLE。任何返回錯誤 1146 的表都表示這些表的創建順序不正確。

解決方案是運行缺少的 CREATE TABLE 並重試或暫時禁用外鍵檢查。在可能存在循環引用的備份恢復期間執行此步驟是必不可少的。

運行以下代碼來修復錯誤：

 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS; SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; -- restore your backup within THIS session SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

約束引用中的表或索引濫用引號

如果您希望診斷此錯誤，請檢查每個 FOREIGN KEY 聲明並確保對象限定符周圍沒有引號，或者表格周圍有引號並且每個列名稱周圍有一對。

這可以通過不引用任何內容或分別引用表和列來解決。

約束引用中的本地鍵、外部表或列有拼寫錯誤

可以通過運行 SHOW TABLES 和 SHOW COLUMNS 並將字符串與 REFERENCES 聲明中指定的字符串進行比較來診斷此錯誤。錯別字一經發現應及時更正。

約束引用的列與外部列的類型或寬度不同

對於診斷，使用 SHOW CREATE TABLE parent 來確保本地列和引用列包含相同類型的數據和寬度。

可以通過編輯 DDL 語句來解決該問題，以確保子表中的列定義與父表中的列定義相匹配。

異物不是任何類型的 KEY

要診斷此問題，請使用 SHOW CREATE TABLE parent 檢查表的 REFERENCES 部分以確保指向它的列未被索引。

通過使該列成為 KEY、UNIQUE KEY 或 PRIMARY KEY 來解決問題。

外鍵是多列PK或UK，其中引用的列不是最左邊的

為了診斷此錯誤，執行 SHOW CREATE TABLE parent 命令以確定 REFERENCES 部分是否指向某個列，該列存在於某些多列索引中但不是其定義中的最左邊。

您可以通過向父表添加索引來解決此問題，其中引用的列是最左邊或唯一的列。

兩個表/列之間的不同字符集/排序規則

要診斷問題，請運行 SHOW CREATE TABLE parent 並驗證子列的 CHARACTER SET 和 COLLATE 部分是否與父表上的部分匹配。

要解決此問題，請確保子表 DDL 與其父表/列的字符集和排序規則相匹配，或者更改父表以使其定義與子表的定義相匹配。

父表沒有使用 InnoDB

可以通過運行 SHOW CREATE TABLE parent 並檢查 ENGINE=INNODB 來診斷該問題。

更改父表以從 MySQL 切換到 InnoDB 以解決此問題。

如何查找“無法添加外鍵約束”錯誤的原因？

這個錯誤並沒有說明MySQL無法對錶進行外鍵約束的具體原因，很籠統。

為幫助解決此問題，您需要確定確切原因。您可以通過在 MySQL 客戶端中運行以下查詢來執行此操作：

 SHOW ENGINE INNODB STATUS;

此查詢將返回三個字段：類型、名稱和狀態。 LATEST FOREIGN KEY ERROR 可以通過展開結果的狀態列來識別。

本節旨在確定上次更改查詢失敗的原因，以便您可以解決問題。

導致此問題的原因通常是主表和外部表中的列類型不匹配。

問題也可能是兩個表的引擎類型不匹配引起的，例如 MyISAM 和 InnoDB。

這也可能是由於表之間的排序規則不同，一個使用 UTF-8，另一個使用 latin1。

您應該注意以下因素：

Engine ：每張表應該配置相同的數據庫引擎，例如InnoDB。

數據類型：在一個表中，兩列應具有相同的“ datatype. int(11) datatype. int(11) ”，而在另一個例子中， smallint(5)會導致問題。

Collation ：為了確保兼容性，兩個列的字符集應該相同，例如，UTF8。

注意：列仍然可以有不同的列，即使您的表具有相同的排序規則。

Unique ：外鍵應該引用引用表中的唯一字段，通常是主字段。

Ungisned ：一個表可能有未簽名的鍵，而另一個表可能沒有任何未簽名的鍵。

如何修復MySQL“#1215 – Cannot Add Foreign Key Constraint”錯誤（4種方法）？

討論了導致此錯誤消息的不同原因後，我們進入了下一步。這個錯誤需要修復，讓我們看看如何用四種有效的方法來修復它。

方法 1：檢查表可用性

要開始排查錯誤，您可以通過運行以下命令檢查該表是否存在：

 show tables;

從這段代碼中可以明顯看出，由於表可用，因此可以創建約束。

方法 2：確保父表正在使用 InnoDB

可以通過檢查數據庫表的存儲引擎集來進一步調查。可以使用以下命令來執行此操作：

 SHOW CREATE TABLE parent

結果是你會發現 InnoDB 不是存儲引擎。由於設置了 ENGINE=MYISAM，您可以通過運行以下命令來更改存儲引擎：

 ALTER TABLE parent ENGINE=INNODB;

最終可以使用此方法修復錯誤。

方法三：檢查是否有不當引用

對於每個 FOREIGN KEY 聲明，請確保沒有在對象限定符周圍插入引號，或者在表名和列名周圍使用單獨的引號。

使用此方法，必須不引用任何內容或分別引用列和表以修復錯誤。

方法四：驗證密鑰是否存在

在輸入MySQL命令時，需要保證輸入了正確的命令。交叉檢查確保客戶沒有引用目標表中不存在的表鍵。

因此，有必要糾正客戶如何使用桌子上的鑰匙。在某些情況下，用於 ON DELETE SET NULL 的列可能沒有空定義。

為防止 MySQL 錯誤 1215，您必須確保該列設置為默認空值。

結論

本文通過四種行之有效的方法解釋了MySQL出現“#1215 – Cannot Add Foreign Key Constraint”錯誤的原因、如何查找原因以及如何修復。

感謝您抽出寶貴時間審閱本文。我們很高興向您展示它。在評論部分，請發表任何問題或意見。

如果您想了解最新消息，請喜歡並關注我們的 Facebook 和 Twitter 頁面。