MySQL報錯Failed to open the referenced table XXX問題

更新時間：2024年11月06日 10:46:11 作者：A_bad_horse

在數(shù)據(jù)庫操作中,嘗試刪除外鍵約束表'master_role'時遇到錯誤碼3730,因其被'user_role'表中的外鍵'fk_user_role'引用,解決方法包括關閉外鍵檢查和刪除外鍵,阿里巴巴開發(fā)手冊和知乎回答指出,外鍵雖能維護數(shù)據(jù)一致性

問題復現(xiàn)

CREATE TABLE `master_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `role` VARCHAR(50) CHARACTER SET latin1 NOT NULL COMMENT 'Role',
  `active` TINYINT NOT NULL COMMENT 'Active',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'Master Role';

CREATE TABLE `user_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` VARCHAR(150) NOT NULL COMMENT 'Email',
  `role_id` INTEGER NOT NULL COMMENT 'Role ID',
  PRIMARY KEY (`id`),
  KEY `fk_user_role_idx` (`role_id`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `master_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'User Role';

step1: create table master_role

step2: create table user_role

step3: DROP TABLE IF EXISTS master_role;

Error Code: 3730. Cannot drop table 'master_role' referenced by a foreign key constraint 'fk_user_role' on table 'user_role'.

解決方案

方案一：關閉外鍵檢查

SET foreign_key_checks=0;  # 關閉外鍵檢查
DROP TABLE Course;
SET foreign_key_checks=1;  # 開啟外鍵檢查

關閉檢查之后，如果下次還要使用外鍵就要再次打開檢查，較為繁瑣。

方案二：刪除外鍵

#user_role表刪除外鍵
ALTER TABLE user_role DROP FOREIGN KEY fk_user_role;
#刪表
DROP TABLE IF EXISTS master_role;

外鍵

阿里規(guī)范

源自《阿里巴巴開發(fā)手冊》

實例：

假設刪除微博某V，其粉絲關系表中的百萬條數(shù)據(jù)如果有外鍵約束，就會影響性能。

來自知乎的回答

外鍵約束是物理上的“硬綁定”，而表的關聯(lián)只是一種邏輯概念，即數(shù)據(jù)間存在一定的聯(lián)系。數(shù)據(jù)庫通過外鍵建立的“硬綁定”會帶來額外的資源消耗來進行一致性和完整性校驗，盡管有時候無需這些校驗。
因此，一般不建議使用外鍵約束來保證數(shù)據(jù)的一致性和完整性。--- 源自知乎

外鍵作用：維護數(shù)據(jù)一致性和完整性，避免借助外部資源（例如：啟用事務）。

何時使用：高并發(fā)大流量事務場景使用外鍵容易造成死鎖，數(shù)據(jù)庫資源會更容易出現(xiàn)瓶頸。一般互聯(lián)網(wǎng)很少使用外鍵。--- 源自知乎

總結(jié)

實際建表時，不建外鍵。

表之間的關聯(lián)可以通過建立索引（咨詢同事得到的答案，后續(xù)會就此問題寫博客解答）。

更新user_role表的創(chuàng)建語句，刪除外鍵。

CREATE TABLE `user_role` (
  `id` INTEGER NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `email` VARCHAR(150) NOT NULL COMMENT 'Email',
  `role_id` INTEGER NOT NULL COMMENT 'Role ID',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARSET=utf8mb4
COMMENT = 'User Role';

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: