且先看如下 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 = '管理员分组表';
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)