CREATE TABLE `test`.`test1` (
`test1_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
`test1_4` VARCHAR(45) NOT NULL,
`test1_5` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test1_1`)
)
ENGINE = InnoDB;
表2如下:
CREATE TABLE `test`.`test2` (
`test2_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test2_2` VARCHAR(45) NOT NULL,
`test2_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test2_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
表3如下:
CREATE TABLE `test`.`test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
在这里创建表1(test1)没问题,创建第表2(test2)也没问题,创建表3(test3)的时候就是 ERROR 1005 (HY000): Can't create table 'test.test3' (errno: 121)这个错误。
然后把表2(test2)删掉然后创建表3(test3)可以创建,表3(test3)创建完了之后再创建表2的时候 还是这个错误。ERROR 1005 (HY000): Can't create table 'test.test2' (errno: 121)
请高手指教。硬盘空间足够。
5 个解决方案
#1
约束名重名了。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
红色的名字与表2时的重复了。改一下。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1_3` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2_3` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
就可以了。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
红色的名字与表2时的重复了。改一下。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1_3` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2_3` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
就可以了。
#3
报错 : ERROR 1005 (HY000): Can't create table 'PIXOS_DB.TB_COMPANY' (errno: -1)
#4
约束名重复了
#5
mysql> CREATE TABLE `test`.`test1` (
-> `test1_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> `test1_4` VARCHAR(45) NOT NULL,
-> `test1_5` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test1_1`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test2` (
-> `test2_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test2_2` VARCHAR(45) NOT NULL,
-> `test2_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test2_1`),
-> CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test3` (
-> `test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test3_2` VARCHAR(45) NOT NULL,
-> `test3_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test3_1`),
-> CONSTRAINT `test1_1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2_1` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_3_1` FOREIGN KEY `test1_3` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql>
-> `test1_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> `test1_4` VARCHAR(45) NOT NULL,
-> `test1_5` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test1_1`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test2` (
-> `test2_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test2_2` VARCHAR(45) NOT NULL,
-> `test2_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test2_1`),
-> CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test3` (
-> `test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test3_2` VARCHAR(45) NOT NULL,
-> `test3_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test3_1`),
-> CONSTRAINT `test1_1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2_1` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_3_1` FOREIGN KEY `test1_3` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql>
#1
约束名重名了。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
红色的名字与表2时的重复了。改一下。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1_3` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2_3` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
就可以了。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
红色的名字与表2时的重复了。改一下。
CREATE TABLE `test3` (
`test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`test3_2` VARCHAR(45) NOT NULL,
`test3_3` VARCHAR(45) NOT NULL,
`test1_1` INTEGER UNSIGNED NOT NULL,
`test1_2` VARCHAR(45) NOT NULL,
`test1_3` VARCHAR(45) NOT NULL,
PRIMARY KEY(`test3_1`),
CONSTRAINT `test1_1_3` FOREIGN KEY `test1_1` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_2_3` FOREIGN KEY `test1_2` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `test1_3_3` FOREIGN KEY `test1_3` (`test1_1`)
REFERENCES `test1` (`test1_1`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = InnoDB;
就可以了。
#2
#3
报错 : ERROR 1005 (HY000): Can't create table 'PIXOS_DB.TB_COMPANY' (errno: -1)
#4
约束名重复了
#5
mysql> CREATE TABLE `test`.`test1` (
-> `test1_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> `test1_4` VARCHAR(45) NOT NULL,
-> `test1_5` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test1_1`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test2` (
-> `test2_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test2_2` VARCHAR(45) NOT NULL,
-> `test2_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test2_1`),
-> CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test3` (
-> `test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test3_2` VARCHAR(45) NOT NULL,
-> `test3_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test3_1`),
-> CONSTRAINT `test1_1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2_1` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_3_1` FOREIGN KEY `test1_3` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql>
-> `test1_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> `test1_4` VARCHAR(45) NOT NULL,
-> `test1_5` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test1_1`)
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test2` (
-> `test2_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test2_2` VARCHAR(45) NOT NULL,
-> `test2_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test2_1`),
-> CONSTRAINT `test1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `test`.`test3` (
-> `test3_1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> `test3_2` VARCHAR(45) NOT NULL,
-> `test3_3` VARCHAR(45) NOT NULL,
-> `test1_1` INTEGER UNSIGNED NOT NULL,
-> `test1_2` VARCHAR(45) NOT NULL,
-> `test1_3` VARCHAR(45) NOT NULL,
-> PRIMARY KEY(`test3_1`),
-> CONSTRAINT `test1_1_1` FOREIGN KEY `test1_1` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_2_1` FOREIGN KEY `test1_2` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT,
-> CONSTRAINT `test1_3_1` FOREIGN KEY `test1_3` (`test1_1`)
-> REFERENCES `test1` (`test1_1`)
-> ON DELETE RESTRICT
-> ON UPDATE RESTRICT
-> )
-> ENGINE = InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql>