ERROR 1005（HY000）：可以't create table ' [shrewd_db.alert_disable_register'（错误号：150）

1 回答

• 3

  为了使外键约束成功，必须满足以下条件：

  • 数据类型1和符号必须匹配

  • 引用的表必须在相关列上具有最左侧2的索引，以便快速进行约束验证 .

  • 整理可以起到一个作用 . 请参阅我的answer .

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

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

  请注意，显示宽度（括号中的数字）和可空性无关紧要 .

  来自Mysql手册页Using FOREIGN KEY Constraints：

  外键和引用键中的对应列必须具有相似的数据类型 . 整数类型的大小和符号必须相同 . 字符串类型的长度不必相同 . 对于非二进制（字符）字符串列，字符集和排序规则必须相同 . MySQL需要外键和引用键的索引，以便外键检查可以快速，不需要表扫描 . 在引用表中，必须有一个索引，其中外键列以相同的顺序列为第一列 . 外键和引用键中的相应列必须具有相似的数据类型 . 整数类型的大小和符号必须相同 . 字符串类型的长度不必相同 . 对于非二进制（字符）字符串列，字符集和排序规则必须相同 .

  另请注意，满足FK关系的引用表键不需要是主键，甚至不是唯一键 . 只是第一个（也称为最左边2）订购满足 .

  同样，索引不是你的问题，但通常是针对其他人 .

  对于需要在创建表后添加外键约束的用户，请使用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;
  

  1类似且允许的列差异，字符串数据

  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
  

  2最左/最多索引排序

  单个列上的索引（a.k.a.一个键）是最左边的，因为它不是复合索引 .

  父列表（引用的）表中的多列索引（也称为复合索引）是最令人满意的，如果其列的顺序与依赖于外键的子表键的顺序相同（FK） ）关系 . 即使该父组合键中的列数大于子组合键的计数 . 见下面的例子 .

  假设子（引用）表具有按 (col1,col4) 排序的复合键FK要求

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

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

  这里的内容是，如果这样的父键不是最令人满意的，那么子表 CREATE TABLE 的语句将因FK关系而失败 . 创建表的尝试将失败，错误代码为1215 .

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

  回复于 2024-05-03T09:12:35+08:00