使用InnoDB引擎创建表时出现MySQL 1005错误 [英] MySQL 1005 error when creating table using InnoDB engine

问题描述

当我尝试创建具有以下定义的表时，

When I try to create a table with the following definition,

CREATE TABLE `demo` (
    `id` INT(11) NOT NULL auto_increment,
    `x_id` INT(11) NOT NULL,
    `y_id` INT(11) NOT NULL,
    `z_id` INT(11) NOT NULL,
    `status` TINYINT unsigned NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY  (`id`),
    CONSTRAINT UNIQUE INDEX(x_id, y_id)
) ENGINE=InnoDB;

发生OperationalError:

_mysql_exceptions.OperationalError:
(1005, "Can't create table 'xxx.frm' (errno: -1)")

如果我删除结尾的ENGINE=InnoDB，它会起作用.

It works if I remove the trailing ENGINE=InnoDB.

这是什么原因?

MySQL版本是 mysql Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (i686) using readline 5.2

The MySQL version is mysql Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (i686) using readline 5.2

推荐答案

如果重新创建已删除的表，则该表必须具有符合引用该表的外键约束的定义.如前所述，它必须具有正确的列名和类型，并且必须在引用的键上具有索引.如果不满足这些要求，MySQL将返回错误号1005，并在错误消息中引用错误150.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

如果MySQL从CREATE TABLE语句报告错误号1005，并且错误消息引用错误150，则表创建失败，因为未正确形成外键约束.类似地，如果ALTER TABLE失败并且引用错误150，则意味着将为更改后的表错误地形成外键定义.您可以使用 SHOW ENGINE INNODB STATUS 显示服务器中最新的InnoDB外键错误的详细说明.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

外键约束-错误1005

这篇关于使用InnoDB引擎创建表时出现MySQL 1005错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！