为什么我收到“错误代码1005无法创建c errno 150 mysql”？ [英] Why am I getting "error code 1005 can t create c errno 150 mysql "?

问题描述

编辑＃2：-
遵循Spencer 7593的回答后，即创建没有外键的表，然后使用<$ c添加它们$ c> alter table 语句，该表已成功创建，并且外键已成功添加。

EDIT # 2:- After following Spencer 7593's answer, i.e. creating the table without the foreign keys, and then adding them using the alter table statement, the table was created successfully and the foreign keys were added successfully.

但是现在我想知道为什么吗？ 为什么在创建表然后使用 alter table 语句添加外键约束时起作用，而不是在 create table 语句？

But now I wonder why? Why does it work when we create the table and then add the foreign key constraints using alter table statement, and not when we do it all in the create table statement?

我正在创建以下表我的数据库。前两个成功创建，但第三个错误。

I am creating the following tables in my database. The first two were created successfully but the third one is giving this error.

我检查了数据库中这个答案，我能理解。请告诉我为什么会出现此错误。

I checked my database for the points in this answer which I could understand. Please tell me why I am getting this error.

CREATE TABLE a (
    a_id INT(255) NOT NULL AUTO_INCREMENT,
    name VARCHAR(5000),
    code VARCHAR(5000),
    PRIMARY KEY (a_id)
);

CREATE TABLE b (
    b_id INT(255) NOT NULL AUTO_INCREMENT,
    b_name VARCHAR(5000),
    PRIMARY KEY (b_id)
);

CREATE TABLE c ( -- error code 1005 can t create c errno 150 mysql
    a_id INT(255), -- fk
    b_id INT(255), -- fk
    PRIMARY KEY (a_id, b_id),
    FOREIGN KEY (a_id)
        REFERENCES a (a_id),
    FOREIGN KEY (b_id)
        REFERENCES b(b_id)
);

---

编辑：-

---

-

我仅分别执行了这三个语句（将所有 INT 类型的大小更改为 11 -感谢Michael Berkowski的评论），令我惊讶的是，它确实创建了表格，但是给出了此日志（如果我使用正确的单词）：

I executed just these three statements separately (after changing the size of all INT types to 11 - thanks to the comment by Michael Berkowski), and to my surprise, it did create the tables, but gave this log (if I am using the right word):

3   4   01:03:49    CREATE TABLE a (
     a_id INT(11) NOT NULL AUTO_INCREMENT,
     name VARCHAR(5000),
     code VARCHAR(5000),
     PRIMARY KEY (a_id)
 )  0 row(s) affected   0.156 sec
3   5   01:03:54    CREATE TABLE b (
     b_id INT(11) NOT NULL AUTO_INCREMENT,
     b_name VARCHAR(5000),
     PRIMARY KEY (b_id)
 )  0 row(s) affected   0.234 sec
3   6   01:03:58    CREATE TABLE c ( -- error code 1005 can t create c errno 150 mysql
     a_id INT(11), -- fk
     b_id INT(11), -- fk
     PRIMARY KEY (a_id, b_id),
     FOREIGN KEY (a_id)
         REFERENCES a (a_id),
     FOREIGN KEY (b_id)
         REFERENCES b(b_id)
 )  0 row(s) affected   0.203 sec

这三个表都已创建，但是在创建第三个表时会返回错误。

编辑＃3：-

输出显示创建表a和显示创建表b：-

Output from SHOW CREATE TABLE a, and SHOW CREATE TABLE b:-

CREATE TABLE `a` (
   `a_id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(5000) DEFAULT NULL,
   `code` varchar(5000) DEFAULT NULL,
   PRIMARY KEY (`a_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b` (
   `b_id` int(11) NOT NULL AUTO_INCREMENT,
   `b_name` varchar(5000) DEFAULT NULL,
   PRIMARY KEY (`b_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

推荐答案

此错误的最常见原因是外键列definit ion与所引用列的定义不匹配。错误的另一个原因是被引用的列在被引用的表中不存在。

The most usual cause of this error is that the foreign key column definition does not match the definition of the referenced column. Another reason for the error is that the column referenced does not exist in the referenced table.

但是OP CREATE TABLE 语句没有这个问题；这些都可以与MyISAM和InnoDB一起用作默认存储引擎。 （不确定其他任何存储引擎，我们没有看到指定的存储引擎，我只是猜测默认值为InnoDB或MyISAM。）

But the OP CREATE TABLE statements don't have that problem; those work with both MyISAM and InnoDB as default storage engine. (Not sure about any other storage engines, we don't see a storage engine specified, and I'm just guessing that the default is either InnoDB or MyISAM.)

SHOW VARIABLES LIKE 'default_storage_engine'

要调试此功能，请尝试运行创建表c ，不带外键约束定义，并验证表是否创建成功。 （长度修饰符值255使我们感到奇怪。为什么不让它默认为11？）

To debug this, try running the create table c WITHOUT the foreign key constraint definitions, and verify the table is created successfully. (The length modifier value of 255 strikes us as odd. Why not allow it to default to 11?)

CREATE TABLE c 
( a_id  INT COMMENT 'FK ref a.a_id'
, b_id  INT COMMENT 'FK ref b.b_id'
, PRIMARY KEY (a_id, b_id)
);

然后尝试添加外键约束：

Then try adding the foreign key constraints:

ALTER TABLE c ADD CONSTRAINT FK_c_a
FOREIGN KEY (a_id) REFERENCES a (a_id);

确保列 a_id 在<$中存在c $ c> a ，并且定义匹配。 （是否有可能将该列命名为 id （而不是 a_id ）在表 a 中？）

Ensure that column a_id exists in a, and that the definition matches. (Is it possible that the column is named id (rather than a_id) in table a?)

这篇关于为什么我收到“错误代码1005无法创建c errno 150 mysql”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！