#1005 -无法创建表errno: 150

I have seen a lot of questions in Stackoerflow before asking this question

在问这个问题之前，我在Stackoerflow中看到了很多问题。

When I execute the below mlm_commission create table query,

当我执行下面的mlm_commission创建表查询时，

I am getting the following error.

我得到了以下错误。

1005 - Can't create table 'mlm_new.mlm_commission' (errno: 150) (Details…)

无法创建表'mlm_new。mlm_commission”(errno:150)(细节…)

Also When I click Details, I got the following text

另外，当我点击Details时，会得到如下文本

Supports transactions, row-level locking, and foreign keys

支持事务、行级锁和外键

Create Commission Table

创建佣金表

CREATE TABLE IF NOT EXISTS mlm_commission
                (`weekno` int(11) NOT NULL,
                `level` int(11) NOT NULL,
                `username` varchar(500) NOT NULL,
                `PositionA` int(11) NOT NULL,
                `CFPositionA` int(11) NOT NULL,
                `PositionB` int(11) NOT NULL,
                `CFPositionB` int(11) NOT NULL,
                `PositionC` int(11) NOT NULL,
                `CFPositionC` int(11) NOT NULL,
                `ABLeft` int(11) NOT NULL,
                `CFABLeft` int(11) NOT NULL,
                `ABRight` int(11) NOT NULL,
                `CFABRight` int(11) NOT NULL,
                `CLeft` int(11) NOT NULL,
                `CFCLeft` int(11) NOT NULL,
                `CRight` int(11) NOT NULL,
                `CFCRight` int(11) NOT NULL,
                `ABMatchingPair` int(11) NOT NULL,
                `CMatchingPair` int(11) NOT NULL,
                `IsIncludedToParent` enum('Y','N'),
                `side` enum('L','R') NOT NULL,
                `leg` enum('0','1','2') NOT NULL,
                `parent_key` varchar(15) NOT NULL,
                `commission` int(11) NOT NULL,
                FOREIGN KEY(`username`) REFERENCES mlm_rtmfx_users(`username`),
                FOREIGN KEY(`side`) REFERENCES mlm_rtmfx_users(`side`),
                FOREIGN KEY(`leg`) REFERENCES mlm_rtmfx_users(`leg`),
                FOREIGN KEY(`parent_key`) REFERENCES mlm_rtmfx_users(`parent_key`),
                PRIMARY KEY(`username`,`weekno`,`level`));

I am referencing 4 foreign key values in mlm_rtmfx_commission table from the below table.

我从下表中引用了mlm_rtmfx_commission表中的4个外键值。

CREATE TABLE `mlm_rtmfx_users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` varchar(1023) NOT NULL,
   `username` varchar(500) NOT NULL,
   `user_key` varchar(15) NOT NULL,
   `parent_key` varchar(15) NOT NULL,
   `sponsor_key` varchar(15) NOT NULL,
   `leg` enum('0','1','2') NOT NULL,
   `payment_status` enum('0','1') NOT NULL,
   `pinno` int(11) NOT NULL,
   `user_password` varchar(8) NOT NULL,
   `side` enum('L','R') NOT NULL,
   `iseligible` enum('Y','N') NOT NULL,
   `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
   )

You can check that each foreign key references are having same name and type.

您可以检查每个外键引用的名称和类型是否相同。

I have seen this Error No:150 and I am confusing about indexes.

我已经看到了这个错误:150，我对索引感到困惑。

I also having the list of indexes in mlm_rtmfx_users table. Please check below.

我也有mlm_rtmfx_users表中的索引列表。请检查下面。

I got the index table from the following query.

我从下面的查询中获得了索引表。

SHOW INDEX FROM mlm_rtmfx_users

Please clarify me where do I go wrong? If my question is not clear, Please let me know.

请告诉我哪里出错了?如果我的问题不清楚，请告诉我。

1 个解决方案

#1

There are two requirements to FOREIGN KEY creation. First, the referencing columns must have exactly the same data types as the referenced columns. Second, the referenced columns must be indexed exactly as the referencing columns are. That is, if you have 3 single FOREIGN KEYs, you need 3 corresponding indices on the referenced table. If you had one compound FOREIGN KEY comprising 3 columns, you would need a corresponding compound index on the same 3 columns in the referenced table.

创建外键需要两个条件。首先，引用列必须与引用列具有完全相同的数据类型。第二，引用的列必须与引用的列一样被索引。也就是说，如果你有3个外键，你需要在引用的表上有3个相应的索引。如果有一个包含3列的复合外键，则需要在引用表中的相同3列上相应的复合索引。

You have some of the columns referenced in mlm_rtmfx_users indexed, but each of the referenced columns must have an index matching that of the FOREIGN KEY columns in the referencing table. So you will need to add indices on side,leg,parent_key,user.

您有一些在mlm_rtmfx_users索引中引用的列，但是每个引用的列必须有一个与引用表中的外键列相匹配的索引。所以你需要在侧面，腿，parent_key,user上添加索引。

If you reduce the length of username, you will not exceed the InnoDB limit of 767 bytes per index.

如果您减少用户名的长度，您将不会超过InnoDB对每个索引767字节的限制。

CREATE TABLE `mlm_rtmfx_users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` varchar(1023) NOT NULL,
   `username` varchar(50) NOT NULL,
   `user_key` varchar(15) NOT NULL,
   `parent_key` varchar(15) NOT NULL,
   `sponsor_key` varchar(15) NOT NULL,
   `leg` enum('0','1','2') NOT NULL,
   `payment_status` enum('0','1') NOT NULL,
   `pinno` int(11) NOT NULL,
   `user_password` varchar(8) NOT NULL,
   `side` enum('L','R') NOT NULL,
   `iseligible` enum('Y','N') NOT NULL,
   `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    -- More indices are needed in the parent table:
    INDEX (`username`),
    INDEX (`side`),
    INDEX (`leg`),
    INDEX (`parent_key`)
 )

If you must keep username with a long length of 500, you will need to enable innodb_long_prefix to increase the permitted byte length of the index.

如果必须保持用户名的长长度为500，则需要启用innodb_long_prefix来增加索引的允许字节长度。

With the additional indices added to the parent table (and the username length reduced into InnoDB's index limit), the tables can successfully be created. Here it is in action

添加到父表的附加索引(用户名长度被降为InnoDB的索引限制)后，可以成功创建表。这就是行动