现在的位置: 首页 > 综合 > 正文

mysql 外键约束不能重名ERROR 1005 (HY000): Can’t create table ‘prize.tb_power’ (errno: 121)

2013年02月27日 ⁄ 综合 ⁄ 共 1839字 ⁄ 字号 评论关闭

且先看如下 sql 语句,直接看有标颜色部分

 31 CREATE  TABLE IF NOT EXISTS `prize`.`tb_group` (

 32   `gid` INT NULL AUTO_INCREMENT ,
 33   `name` VARCHAR(45) NOT NULL ,
 34   PRIMARY KEY (`gid`) )
 35 ENGINE = InnoDB
 36 DEFAULT CHARACTER SET = utf8
 37 COLLATE = utf8_general_ci
 38 COMMENT = '管理员分组表';

 41 -- -----------------------------------------------------
 42 -- Table `prize`.`tb_manager_group`
 43 -- -----------------------------------------------------
 44 DROP TABLE IF EXISTS `prize`.`tb_manager_group` ;
 45 
 46 CREATE  TABLE IF NOT EXISTS `prize`.`tb_manager_group` (
 47   `mg_id` INT NULL AUTO_INCREMENT ,
 48   `gid` INT NOT NULL ,
 49   `mid` INT NOT NULL ,
 50   PRIMARY KEY (`mg_id`) ,
 51   INDEX (`gid` ASC) ,
 52   INDEX `mid` (`mid` ASC) ,
 53   CONSTRAINT `gid`
 54     FOREIGN KEY (`gid` )
 55     REFERENCES `prize`.`tb_group` (`gid` )
 56     ON DELETE CASCADE
 57     ON UPDATE CASCADE,
 58   CONSTRAINT `mid`
 59     FOREIGN KEY (`mid` )
 60     REFERENCES `prize`.`tb_manager` (`mid` )
 61     ON DELETE RESTRICT
 62     ON UPDATE RESTRICT)
 63 ENGINE = InnoDB
 64 DEFAULT CHARACTER SET = utf8

 65 COLLATE = utf8_general_ci;

 83 -- -----------------------------------------------------
 84 -- Table `prize`.`tb_power`
 85 -- -----------------------------------------------------
 86 DROP TABLE IF EXISTS `prize`.`tb_power` ;
 87 
 88 CREATE  TABLE IF NOT EXISTS `prize`.`tb_power` (
 89   `power_id` INT NULL AUTO_INCREMENT ,
 90   `gid` INT NOT NULL ,
 91   `url_id` INT NOT NULL ,
 92   PRIMARY KEY (`power_id`) ,
 93   INDEX `url_id` (`url_id` ASC) ,
 94   INDEX `group_id` (`gid` ASC) ,
 95   CONSTRAINT `url_id`
 96     FOREIGN KEY (`url_id` )
 97     REFERENCES `prize`.`tb_url` (`url_id` )
 98     ON DELETE CASCADE
 99     ON UPDATE CASCADE,
100   CONSTRAINT `group_id`
101     FOREIGN KEY (`gid` )
102     REFERENCES `prize`.`tb_group` (`gid` )
103     ON DELETE RESTRICT
104     ON UPDATE RESTRICT)
105 ENGINE = InnoDB
106 DEFAULT CHARACTER SET = utf8
107 COLLATE = utf8_general_ci;

如上所示:gid 为表 tb_group 的主键, 我在 tb_manager_group 与 tb_power 中将其置为外键并约束,此时我们两表的 constraint 名不可重复,如果重复(如:都为 gid )则报错:ERROR 1005 (HY000): Can't create table 'prize.tb_power' (errno: 121)

抱歉!评论已关闭.