mysql - 错误 1005 (HY000) : Can't create table 'shrewd_db.alert_disable_register' (errno: 150)

Question

我想通过运行以下 SQL 在 MySQL 中创建一个表，

CREATE TABLE IF NOT EXISTS `shrewd_db`.`alert_disable_register` (
  `id_alert_disable_register` MEDIUMINT NOT NULL AUTO_INCREMENT,
  `id_label` MEDIUMINT UNSIGNED NULL,
  `id_indicator` MEDIUMINT UNSIGNED NULL,
  `id_user` MEDIUMINT UNSIGNED NULL,
  `active` TINYINT(1) NULL DEFAULT 1,
  `id_alert_disable_rule` MEDIUMINT NULL,
  `id_escalation_plan` INT NULL,
  PRIMARY KEY (`id_alert_disable_register`),
  INDEX `id_escalation_plan_alert_rule_idx` (`id_alert_disable_rule` ASC),
  INDEX `id_label_idx` (`id_label` ASC),
  INDEX `id_indicator_idx` (`id_indicator` ASC),
  INDEX `id_user_idx` (`id_user` ASC),
  INDEX `id_escalation_plan_idx` (`id_escalation_plan` ASC),
  CONSTRAINT `id_label`
    FOREIGN KEY (`id_label`)
    REFERENCES `shrewd_db`.`escalation_plan` (`id_label`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_indicator`
    FOREIGN KEY (`id_indicator`)
    REFERENCES `shrewd_db`.`escalation_plan` (`id_indicator`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_user`
    FOREIGN KEY (`id_user`)
    REFERENCES `shrewd_db`.`escalation_plan_task_group_has_user` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_alert_disable_rule`
    FOREIGN KEY (`id_alert_disable_rule`)
    REFERENCES `shrewd_db`.`alert_disable_rule` (`id_alert_disable_rule`)
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `id_escalation_plan`
    FOREIGN KEY (`id_escalation_plan`)
    REFERENCES `shrewd_db`.`escalation_plan` (`id_escalation_plan`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

但是我遇到了以下错误，

ERROR 1005 (HY000): Can't create table 'shrewd_db.alert_disable_register' (errno: 150)

谁能帮我解决这个问题，:)

请在下方找到创建其他所需表格的脚本，

CREATE TABLE `escalation_plan` (
  `id_escalation_plan` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_indicator` mediumint(8) unsigned NOT NULL,
  `id_label` mediumint(8) unsigned NOT NULL,
  `pressure_waiting_hrs` int(11) NOT NULL DEFAULT '6',
  PRIMARY KEY (`id_escalation_plan`),
  KEY `fk_escalation_plan_escalation_plan1_idx` (`id_indicator`),
  KEY `fk_escalation_plan_label1_idx` (`id_label`),
  CONSTRAINT `fk_escalation_plan_escalation_plan1` FOREIGN KEY (`id_indicator`) REFERENCES `indicator` (`id_indicator`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_escalation_plan_label1` FOREIGN KEY (`id_label`) REFERENCES `label` (`id_label`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8;


CREATE TABLE `escalation_plan_task_group_has_user` (
  `id_escalation_plan_task_has_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` mediumint(8) unsigned NOT NULL,
  `id_escalation_plan_task_group` int(11) NOT NULL,
  `text_alert` tinyint(1) NOT NULL DEFAULT '1',
  `email_alert` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_escalation_plan_task_has_user`),
  KEY `fk_escalation_plan_task_has_user_user1_idx` (`id_user`),
  KEY `fk_escalation_plan_task_group_has_user_escalation_plan_task_idx` (`id_escalation_plan_task_group`),
  CONSTRAINT `fk_escalation_plan_task_group_has_user_escalation_plan_task_g1` FOREIGN KEY (`id_escalation_plan_task_group`) REFERENCES `escalation_plan_task_group` (`id_escalation_plan_task_group`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_escalation_plan_task_has_user_user1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3605 DEFAULT CHARSET=utf8;


CREATE TABLE `alert_disable_rule` (
  `id_alert_disable_rule` mediumint(9) NOT NULL AUTO_INCREMENT,
  `disable_in_weekend` tinyint(1) DEFAULT '0',
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `start_time` decimal(10,0) DEFAULT NULL,
  `end_time` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id_alert_disable_rule`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

为了使外键约束成功，除其他事项外，必须满足以下条件:

1. 数据类型¹和符号必须匹配
2. 引用的表必须在相关列上有一个最左边的² 索引，以便快速进行约束验证。
3. 整理可以起到一定的作用。请看answer我的。

在您的情况下，索引很好，但正如 Solarflare 所提到的，这里只有您的数据类型才是重要的并且不匹配:

`alert_disable_register`.`id_escalation_plan`-- signed int
`escalation_plan`.`id_escalation_plan` -- unsigned int

请注意，显示宽度(括号中的数字)和可空性无关紧要。

来自 Mysql 手册页 Using FOREIGN KEY Constraints :

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

另请注意，满足FK关系的引用表键不一定是Primary Key甚至Unique key。仅最先(也称为最左²)满足排序。

同样，索引不是您的问题，但通常是其他人的问题。

对于那些需要在创建表后添加外键约束的人，​​使用ALTER TABLE声明。

以下测试将正常运行。不过，您需要自行决定如何处理更改。您遗漏了一些提供的表格，这些表格需要删除前 2 个表格中的一些 FK 约束。

create database xyztest123;
use xyztest123;


CREATE TABLE `escalation_plan` (
  `id_escalation_plan` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_indicator` mediumint(8) unsigned NOT NULL,
  `id_label` mediumint(8) unsigned NOT NULL,
  `pressure_waiting_hrs` int(11) NOT NULL DEFAULT '6',
  PRIMARY KEY (`id_escalation_plan`),
  KEY `fk_escalation_plan_escalation_plan1_idx` (`id_indicator`),
  KEY `fk_escalation_plan_label1_idx` (`id_label`)
  -- CONSTRAINT `fk_escalation_plan_escalation_plan1` FOREIGN KEY (`id_indicator`) REFERENCES `indicator` (`id_indicator`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  -- CONSTRAINT `fk_escalation_plan_label1` FOREIGN KEY (`id_label`) REFERENCES `label` (`id_label`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8;


CREATE TABLE `escalation_plan_task_group_has_user` (
  `id_escalation_plan_task_has_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_user` mediumint(8) unsigned NOT NULL,
  `id_escalation_plan_task_group` int(11) NOT NULL,
  `text_alert` tinyint(1) NOT NULL DEFAULT '1',
  `email_alert` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id_escalation_plan_task_has_user`),
  KEY `fk_escalation_plan_task_has_user_user1_idx` (`id_user`),
  KEY `fk_escalation_plan_task_group_has_user_escalation_plan_task_idx` (`id_escalation_plan_task_group`)
  -- CONSTRAINT `fk_escalation_plan_task_group_has_user_escalation_plan_task_g1` FOREIGN KEY (`id_escalation_plan_task_group`) REFERENCES `escalation_plan_task_group` (`id_escalation_plan_task_group`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  -- CONSTRAINT `fk_escalation_plan_task_has_user_user1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3605 DEFAULT CHARSET=utf8;


CREATE TABLE `alert_disable_rule` (
  `id_alert_disable_rule` mediumint(9) NOT NULL AUTO_INCREMENT,
  `disable_in_weekend` tinyint(1) DEFAULT '0',
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `start_time` decimal(10,0) DEFAULT NULL,
  `end_time` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id_alert_disable_rule`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS  `alert_disable_register` (
  `id_alert_disable_register` MEDIUMINT NOT NULL AUTO_INCREMENT,
  `id_label` MEDIUMINT UNSIGNED NULL,
  `id_indicator` MEDIUMINT UNSIGNED NULL,
  `id_user` MEDIUMINT UNSIGNED NULL,
  `active` TINYINT(1) NULL DEFAULT 1,
  `id_alert_disable_rule` MEDIUMINT NULL,
  `id_escalation_plan` INT unsigned NULL,
  PRIMARY KEY (`id_alert_disable_register`),
  INDEX `id_escalation_plan_alert_rule_idx` (`id_alert_disable_rule` ASC),
  INDEX `id_label_idx` (`id_label` ASC),
  INDEX `id_indicator_idx` (`id_indicator` ASC),
  INDEX `id_user_idx` (`id_user` ASC),
  INDEX `id_escalation_plan_idx` (`id_escalation_plan` ASC),
  CONSTRAINT `id_label`
    FOREIGN KEY (`id_label`) -- MEDIUMINT UNSIGNED 
    REFERENCES  `escalation_plan` (`id_label`) -- mediumint(8) unsigned , -- Index OK?: Yes
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_indicator`
    FOREIGN KEY (`id_indicator`) -- MEDIUMINT UNSIGNED 
    REFERENCES  `escalation_plan` (`id_indicator`) -- mediumint(8) unsigned, -- Index OK?: Yes
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_user`
    FOREIGN KEY (`id_user`) -- MEDIUMINT UNSIGNED  
    REFERENCES  `escalation_plan_task_group_has_user` (`id_user`) -- mediumint(8) unsigned, -- Index OK?: Yes
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_alert_disable_rule`
    FOREIGN KEY (`id_alert_disable_rule`) -- MEDIUMINT 
    REFERENCES  `alert_disable_rule` (`id_alert_disable_rule`) -- mediumint(9),  -- Index OK?: Yes
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT `id_escalation_plan`
    FOREIGN KEY (`id_escalation_plan`) -- INT 
    REFERENCES  `escalation_plan` (`id_escalation_plan`) -- int(10) unsigned, Index OK?: Yes
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

drop database xyztest123;

¹ 相似且允许的列差异，字符串数据

drop table if exists a2; -- must do in reverse order
drop table if exists a1;
create table a1
(   id int auto_increment primary key,
    thing varchar(100) not null,
    key `keyname001` (thing)
)ENGINE = InnoDB;
create table a2
(   id int auto_increment primary key,
    myThing char(40) not null, -- similar and allowable datatype
    foreign key `fk_002` (myThing) references a1(thing)
)ENGINE = InnoDB;
insert a2(myThing) values ('a'); -- error 1452, FK violation
insert a1(thing) values ('a'); -- ok
insert a2(myThing) values ('a'); -- ok, not FK violation

-- now a redo below to show it slightly different

drop table if exists a2; -- must do in reverse order
drop table if exists a1;
create table a1
(   id int auto_increment primary key,
    thing varchar(100) not null,
    key `keyname001` (thing)
)ENGINE = InnoDB;
create table a2
(   id int auto_increment primary key,
    myThing varchar(30) not null, -- similar and allowable datatype
    key(myThing),
    foreign key `fk_002` (myThing) references a1(thing)
)ENGINE = InnoDB;
insert a2(myThing) values ('a'); -- error 1452, FK violation
insert a1(thing) values ('a'); -- ok
insert a2(myThing) values ('a'); -- ok, not FK violation

² 最左边/最前面的索引排序

单个列上的索引(又名键)位于最左侧，因为它不是复合索引。

如果其列的顺序在与依赖于它的外键 (FK) 关系的子表键的顺序相同。即使该父组合键中的列数大于子组合键的列数。请参阅下面的示例。

假设一个子表(引用)有一个复合键 FK 要求按 (col1,col4) 排序

1. 按(col1,col2,col3,col4) 排序的父组合键不满足最左边的要求。

2. 按 (col1,col4,col3, ...) 排序的父组合键确实满足最左边的要求。

这里的要点是，如果这样的父键不满足最左边的要求，那么对于 FK 关系，子表 CREATE TABLE 的语句将失败。创建表的尝试将简单地失败，错误代码为 1215。

同样，对于存在的子项的 ALTER TABLE 将在事后尝试添加 FK 关系时失败。