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

I have troubles with forward engineering my MySQL database into WAMP server.. I was going to post an image of the schema but as this is my first post I can't.

我在将我的MySQL数据库转发到WAMP服务器时遇到了麻烦。我本来打算贴一个图式的图片，但是因为这是我的第一个帖子，所以我不能。

Below is the executed script..

下面是已执行的脚本。

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', '[e-mail]', 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', '[e-mail]', 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', '[e-mail]', 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', '[e-mail]', 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', '[e-mail]', 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', '[e-mail]', 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;

But then I get this error:

然后我得到了这个错误

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

I can't figure out why. Can anyone help me?

我不知道为什么。谁能帮我吗?

8 个解决方案

#1

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:

要检查约束，请使用以下SQL查询:

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.

在那里查找更多信息，或者尝试查看错误发生的位置。对我来说，外文钥匙是个问题。

#2

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:

@Dorvalla的回答和上面提到的这篇博文都为我指明了解决问题的正确方向;从后者引用:

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:

我不知道。我已经根据下面的模式更改了我的外键约束名称，该模式似乎也被Ruby on Rails应用程序使用:

<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>_fk

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

例如，对于OP的表，这将是Link_lession_id_fk。

#3

You can login to mysql and type

您可以登录到mysql并输入

mysql> SHOW INNODB STATUS\G

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

你会有所有的输出，你应该对误差有更好的了解。

#4

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.

如果在某个表中有一个外键定义，而外键的名称在其他地方用作另一个外键，则会出现这个错误。

#5

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.

我假设数据库不包含数据，所以删除它不会影响。

#6

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.

我遇到了这个错误(errno 121)，但是它是由mysql创建的中间表造成的，这些中间表是孤立的，它阻止我修改一个表，即使在我的任何表中都不存在这样的约束名。在某些时候，我的MySQL崩溃或无法清理中间表(以#sql开头的表名)，这导致我出现了一个错误，例如:在尝试运行具有特定约束名的ALTER table时，无法创建表'#sql-' (errno 121)。

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:

根据http://devmy.sql.com/doc/refman/5.7/en/innodb - troubleshooting-datradict.html上的文档，您可以用以下方法搜索这些孤立表:

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:

我使用的版本是5.1，但是上面的命令只适用于>= 5.6版本(手册中关于它可以在5.5或更早版本中工作的说法是错误的，因为INNODB_SYS_TABLES在这些版本中不存在)。通过在命令行中搜索mysql数据目录，我能够找到孤立的临时表(与消息中指定的表不匹配):

find . -iname '#*'

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

在发现了文件名之后，例如#sql-9ad_15.frm，我可以在MySQL中删除孤立的表:

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

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

这样做之后，我就可以成功地运行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."

为了完整起见，正如MySQL文档所链接的那样，“#mysql50#前缀告诉MySQL忽略在MySQL 5.1中引入的文件名安全编码。”

#7

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!

我注意到我的数据库中有“other_database”和“other_database”。这导致了这个问题，因为我实际上在其他数据库中有相同的引用，导致了这个神秘的错误!

#8

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

但对我来说，只有这样才能有所帮助。备份当前的DB 2。删除DB(不是所有表，而是DB) 3。创建DB(检查是否还有previ) 4。从备份恢复数据库