错误: 错误1005: 无法创建表(errno: 121)

我有麻烦与 forward engineering我的 MySQL 数据库进入 WAMP 服务器..。 我本来打算发布一个模式的图片，但是因为这是我的第一个帖子，所以我不能发布。

下面是执行的脚本. 。

use aquaticstar;


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Table `Students`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Students` ;


CREATE  TABLE IF NOT EXISTS `Students` (
`id` VARCHAR(10) NOT NULL ,
`studentName` VARCHAR(45) NOT NULL ,
`gender` CHAR NOT NULL ,
`birthDate` DATETIME NOT NULL ,
`mNo` VARCHAR(10) NOT NULL ,
`contactName` VARCHAR(45) NOT NULL ,
`contactEmail` VARCHAR(45) NOT NULL ,
`contactPhone` INT(10) NOT NULL ,
`startDate` DATETIME NOT NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Waiting List`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Waiting List` ;


CREATE  TABLE IF NOT EXISTS `Waiting List` (
`wait_id` VARCHAR(5) NOT NULL ,
`name` VARCHAR(45) NULL ,
`contactName` VARCHAR(45) NULL ,
`contactPhone` INT(10) NULL ,
`contactEmail` VARCHAR(45) NULL ,
`status` CHAR NULL ,
`remarks` VARCHAR(200) NULL ,
PRIMARY KEY (`wait_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Schedule`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Schedule` ;


CREATE  TABLE IF NOT EXISTS `Schedule` (
`lesson_id` VARCHAR(10) NOT NULL ,
`day` VARCHAR(3) NOT NULL ,
`branch` VARCHAR(30) NOT NULL ,
`level` VARCHAR(30) NOT NULL ,
`time` TIME NOT NULL ,
`ae` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`lesson_id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Link`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Link` ;


CREATE  TABLE IF NOT EXISTS `Link` (
`link_id` VARCHAR(10) NOT NULL ,
`id` VARCHAR(10) NOT NULL ,
`lesson_id` VARCHAR(10) NOT NULL ,
PRIMARY KEY (`link_id`) ,
INDEX `id_idx` (`id` ASC) ,
INDEX `lesson_id_idx` (`lesson_id` ASC) ,
CONSTRAINT `id`
FOREIGN KEY (`id` )
REFERENCES `Students` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `lesson_id`
FOREIGN KEY (`lesson_id` )
REFERENCES `Schedule` (`lesson_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Attendance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Attendance` ;


CREATE  TABLE IF NOT EXISTS `Attendance` (
`date` DATETIME NOT NULL ,
`attendance` VARCHAR(5) NOT NULL ,
`link_id` VARCHAR(10) NOT NULL ,
INDEX `link_id_idx` (`link_id` ASC) ,
CONSTRAINT `link_id`
FOREIGN KEY (`link_id` )
REFERENCES `Link` (`link_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;




SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


-- -----------------------------------------------------
-- Data for table `Students`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s001', 'Sam Khew', 'm', '12/12/1991', 'nm', 'May Khew', 'may@gmail.com', 0198829387, '12/07/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s002', 'Joe Biden', 'm', '13/03/2003', 'nm', 'Layla Biden', 'layla@gmail.com', 0199283763, '14/05/2011', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s003', 'Bob Builder', 'm', '14/02/2002', 'LK920K', 'Mama Builder', 'mama@yahoo.com', 0167728376, '29/02/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s004', 'Kenny Koh', 'm', '18/02/1999', 'MM992', 'Lisa Koh', 'lk@hotmail.com', 0123160231, '19/01/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s005', 'Jane Doe', 'f', '29/09/1999', 'nm', 'Jackie Doe', 'jackied@gmail.com', 0127736254, '02/03/2012', NULL);
INSERT INTO `Students` (`id`, `studentName`, `gender`, `birthDate`, `mNo`, `contactName`, `contactEmail`, `contactPhone`, `startDate`, `remarks`) VALUES ('s006', 'Lola Lai', 'f', '02/05/2004', 'nm', 'Mark Lai', 'mark@gmail.com', 0198827365, '11/09/2011', NULL);


COMMIT;


-- -----------------------------------------------------
-- Data for table `Schedule`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s4', 'Sat', 'Sunway', 'basic', '4pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s5', 'Sat', 'Sunway', 'basic', '5pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat1_s6', 'Sat', 'Sunway', 'basic', '6pm', 'Aini');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s4', 'Sat', 'Sunway', 'advance', '4pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat2_s5', 'Sat', 'Sunway', 'advance', '5pm', 'Nina');
INSERT INTO `Schedule` (`lesson_id`, `day`, `branch`, `level`, `time`, `ae`) VALUES ('sat3_s6', 'Sat', 'Sunway', 'pre-comp', '6pm', 'Marcus');


COMMIT;


-- -----------------------------------------------------
-- Data for table `Link`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L001', 's001', 'sat1_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L002', 's002', 'sat1_s5');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L003', 's003', 'sat1_s6');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L004', 's004', 'sat2_s4');
INSERT INTO `Link` (`link_id`, `id`, `lesson_id`) VALUES ('L005', 's005', 'sat1_s5');


COMMIT;


-- -----------------------------------------------------
-- Data for table `Attendance`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `Attendance` (`date`, `attendance`, `link_id`) VALUES ('26/9/2012', '1', NULL);


COMMIT;

但是我得到了这个错误:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'aquaticstar.link' (errno: 121)

我不知道为什么，有人能帮我吗？

117398 次浏览
小开

You can login to mysql and type

mysql> SHOW INNODB STATUS\G

You will have all the output and you should have a better idea of what the error is.

小开
最佳答案

I searched quickly for you, and it brought me here. I quote:

You will get this message if you're trying to add a constraint with a name that's already used somewhere else

To check constraints use the following SQL query:

SELECT
constraint_name,
table_name
FROM
information_schema.table_constraints
WHERE
constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
constraint_name;

Look for more information there, or try to see where the error occurs. Looks like a problem with a foreign key to me.

小开

If you want to fix quickly, Forward Engineer again and check "Generate DROP SCHEMA" option and proceed.

I assume the database doesn't contain data, so dropping it won't affect.

小开

Foreign Key Constraint Names Have to be Unique Within a Database

Both @Dorvalla’s answer and this blog post mentioned above pointed me into the right direction to fix the problem for myself; quoting from the latter:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database.

I wasn’t aware of that. I have changed my foreign key constraint names according to the following schema which appears to be used by Ruby on Rails applications, too:

<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fk

For the OP’s table this would be Link_lession_id_fk, for example.

小开

If you have a foreign key definition in some table and the name of the foreign key is used elsewhere as another foreign key you will have this error.

小开 
mysql> SHOW ENGINE INNODB STATUS;

But in my case only this way could help:
1. Make backup of current DB
2. Drop DB (not all tables, but DB)
3. Create DB (check that you still have previleges)
4. Restore DB from backup

小开

Something I noticed was that I had "other_database" and "Other_Database" in my databases. That caused this problem as I actually had same reference in other database which caused this mysterious error!

小开

I faced this error (errno 121) but it was caused by mysql-created intermediate tables that had been orphaned, preventing me from altering a table even though no such constraint name existed across any of my tables. At some point, my MySQL had crashed or failed to cleanup an intermediate table (table name starting with a #sql-) which ended up presenting me with an error such as: Can't create table '#sql-' (errno 121) when trying to run an ALTER TABLE with certain constraint names.

According to the docs at http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html , you can search for these orphan tables with:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

The version I was working with was 5.1, but the above command only works on versions >= 5.6 (manual is incorrect about it working for 5.5 or earlier, because INNODB_SYS_TABLES does not exist in such versions). I was able to find the orphaned temporary table (which did not match the one named in the message) by searching my mysql data directory in command line:

find . -iname '#*'

After discovering the filename, such as #sql-9ad_15.frm, I was able to drop that orphaned table in MySQL:

USE myschema;
DROP TABLE `#mysql50##sql-9ad_15`;

After doing so, I was then able to successfully run my ALTER TABLE.

For completeness, as per the MySQL documentation linked, "the #mysql50# prefix tells MySQL to ignore file name safe encoding introduced in MySQL 5.1."


提交答案