Archives

The List

« IFNULL :: MySQL Function of the Day | Main | REVERSE() :: MySQL Function of the Day »

MySQL errno 150

By Carsten | April 10, 2008

The problem: You’re creating an (InnoDB) table with foreign key constraints:

CREATE TABLE a (
  b INT NOT NULL,
FOREIGN KEY b REFERENCES c(d)
) ENGINE=InnoDB;

and MySQL throws an ERROR 1005 (HY000): Can't create table ... (errno: 150)

In 95% of the cases, this means you need to create an index on c(d):

ALTER TABLE c ADD PRIMARY KEY (d)

Topics: MySQL |

9 Responses to “MySQL errno 150”

  1. Ronald Bradford Says:
    April 10th, 2008 at 3:46 pm

    Innodb tables require a Primary Key. If one does not exist, and a defined Unique Key is not promoted internally by Innodb, an internal 6 byte primary key is created, but never accessible.

    In this example, I’d always recommend an Innodb table have a defined primary key.

    As a general principle and best practice, all tables should have primary keys, unless there is a specific reason why not to.

  2. Zahariash Says:
    April 10th, 2008 at 4:28 pm

    Any type of index. Primary key isn’t required.

  3. Bill Karwin Says:
    April 10th, 2008 at 4:42 pm

    I think in the other 5% of cases, it likely means you have declared a primary key in the referenced table, but it has a data type incompatible with your foreign key.

    Make sure to match:

    signed vs. unsigned
    int vs. bigint vs. smallint
    int vs. float vs. numeric
    character set

    Character column length doesn’t seem to matter. A CHAR(10) primary key can be referenced by a CHAR(15) foreign key.

  4. Roland Bouman Says:
    April 10th, 2008 at 4:56 pm

    Actually my experience is that an index is created automatically. I see this problem most often because of data type incompatibility - nasty small things like there is a referenced column that is a signed integer, whereas the referencing column is unsigned, or for (var)char columns, the collations or charactersets aren’t identical.

    Tip: if you have multiple foreign keys, it may not be obvious which one InnoDB is complaining about. In that case, use:

    SHOW ENGINE INNODB STATUS;

    In the output you then get the section that goes:

    ————————
    LATEST FOREIGN KEY ERROR
    ————————
    080410 17:50:45 Error in foreign key constraint of table test/c:
    foreign key(id) references p(id)):
    ….

    which nicely indicates which one you should be looking at.

  5. Carsten Says:
    April 10th, 2008 at 8:56 pm

    All: Thanks for your valuable comments.

    @Roland: Just one thing: as far as I’ve been able to determine, indexes are not created automatically on the target table, only on the referring table.

  6. Dajve Says:
    January 28th, 2009 at 7:24 pm

    To Bill Karwin:

    Thank you for your comment - my table fell into the 5% with unmathing keys (signed vs unsigned in this case) and, after much head-scratching and trawling, switching to both unsigned fixed things for me.

    Thanks again,
    Dajve

  7. Arthur Von Eschen Says:
    March 4th, 2009 at 10:17 pm

    Another time this problem can happen is when one table is using the MyISAM table engine and the other table is using the InnoDB table engine.

  8. Andrew Says:
    December 8th, 2009 at 5:14 am

    Thank you so much! I’ve searched all over and this finally solved my problem. I just needed to create an index as you said.

  9. Beatrice Says:
    January 26th, 2010 at 6:36 pm

    Hello,
    I have a same probleme.
    my table tb_aid has a primery key cod_aid
    my table tb_cout_aid has a primery key (cod_aid, dt_cout)
    cod_aid in the both table have a same type (varchar(5) utf8)
    the both table are Innobd
    I create my foreign key after the tables creation.
    Do you have an idea about the other 5% of cases ?

Comments