MySQL:无法创建表(错误号:150)

问题描述：

我正在尝试导入 .sql 文件，但在创建表时失败.

I am trying to import a .sql file and its failing on creating tables.

这是失败的查询:

CREATE TABLE `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我从同一个数据库导出了 .sql，我删除了所有表，现在我试图导入它，为什么它失败了?

I exported the .sql from the same database, I dropped all the tables and now im trying to import it, why is it failing?

MySQL: 无法创建表 './dbname/data.frm' (errno: 150)

MySQL: Can't create table './dbname/data.frm' (errno: 150)

答

来自 MySQL - 外键约束文档:

如果你重新创建一个被删除的表，它必须有一个符合引用它的外键约束的定义.如前所述，它必须具有正确的列名和类型，并且必须在引用的键上具有索引.如果这些都不满足，MySQL 会返回 Error 1005 并在错误消息中引用 Error 150，这意味着没有正确形成外键约束. 同样，如果由于错误 150，ALTER TABLE 失败，这意味着对于更改后的表，外键定义的格式不正确.

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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

MySQL:无法创建表(错误号:150)

相关推荐