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.

Community
  • 1
  • 1
Guna
  • 74
  • 11
  • Does `username` need to have a length of 500? MySQL will refuse to index a VARCHAR column that large. – Michael Berkowski Jul 19 '14 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. – VMai Jul 19 '14 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._ http://dev.mysql.com/doc/refman/5.0/en/create-index.html – Michael Berkowski Jul 19 '14 at 13:28
  • What is mainly missing are _individual_ indices on the non-PK columns in the referenced table. – Michael Berkowski Jul 19 '14 at 13:29
  • @MichaelBerkowski `username` max length can be reduced to 50. – Guna Jul 19 '14 at 13:32
  • @Guna Do you have [innodb_large_prefix](http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix) enabled? – Michael Berkowski Jul 19 '14 at 13:33
  • @VMai In the `mlm_rtmfx_users` table, I don't have index key for `side`, `leg`,and `parent_key` – Guna Jul 19 '14 at 13:33
  • 1
    @Guna But you reference them, i.e. `FOREIGN KEY( side) REFERENCES mlm_rtmfx_users( side ),` Have a look at your own statement please. – VMai Jul 19 '14 at 13:36

1 Answers1

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

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390