3

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

When I execute the below mlm_commission create table query,

I am getting the following error.

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

Also When I click Details, I got the following text

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.

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.

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

mlm_rtmfx_users index

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.

Improve this question
8
  • Does username need to have a length of 500? MySQL will refuse to index a VARCHAR column that large. Commented Jul 19, 2014 at 13:28
  • please update your CREATE TABLE statement with the output of SHOW CREATE TABLE mlm_rtmfx_users. Even in your screenshot I can't see any key on side, leg and parent_key. All those are you referencing. Commented Jul 19, 2014 at 13:28
  • Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables. dev.mysql.com/doc/refman/5.0/en/create-index.html Commented Jul 19, 2014 at 13:28
  • What is mainly missing are individual indices on the non-PK columns in the referenced table. Commented Jul 19, 2014 at 13:29
  • 1
    @Guna But you reference them, i.e. FOREIGN KEY( side) REFERENCES mlm_rtmfx_users( side ), Have a look at your own statement please. Commented Jul 19, 2014 at 13:36

1 Answer 1

Reset to default
3

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.

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.

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

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.

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your help. I learnt that if an attribute is referenced, It must be indexed in the parent table. Am I right?
That is right. I just added a paragraph to the top explaining that better.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.