MySQL (errno: 150)

January 17th, 2008

Just quick note for anyone getting this error. It took me a little while to figure out.
In my case I was trying to add a foreign key with delete on cascade:

ALTER TABLE table_one
ADD CONSTRAINT name_fk FOREIGN KEY (iTable1ID)
EFERENCES table_two(iTable2ID) ON DELETE CASCADE;

And HeidiSQL was spitting out this error msg:

SQL Error: Can’t create table ‘.\database_name\#sql-72c_fb.frm’ (errno: 150)

How to resolve this error:
Make sure your fields datatype match.
They both should have exactly the same datatype and length.
Eg. INT(4) Unsigned isn’t the same as INT(11)

Also make sure if you have data in the table, the foreign keys will not fail.
Eg. If you have an value in one table and not in the other table, it will fail.

This may be common knowledge to most of you but it might come in handy to someone else.

Entry Filed under: Errors,MySQL

Bookmark and Share   Del.icio.us   Technorati

9 Comments

  • 1. myyellowshoe&hellip  |  2008-03-08 at 4.46 am

    Hey there,
    Thanks for the clear write up on this issue, though I might have found yet another caveat. Take a gander.
    This code will run up untill I ad the foreign key. Funny part is, it was working like yesterday. Which I added an on cascade delete, though it is removed in this.
    Gotta love sql! :D

    CREATE TABLE vik (
    vik_id INT NOT NULL AUTO_INCREMENT,
    nid INT NOT NULL,
    description BLOB,
    PRIMARY KEY(vik_id),
    energy_id INT
    )ENGINE = INNODB;

    CREATE TABLE vik_energy(
    vik_id INT,
    energy_id INT,
    FOREIGN KEY(vik_id) REFERENCES vik(vik_id)

    )ENGINE=INNODB;

    ALTER TABLE vik ADD FOREIGN KEY(energy_id) REFERENCES vik_energy(energy_id);

  • 2. D Miller&hellip  |  2008-06-24 at 6.42 am

    Thanks!!

  • 3. Liam O'Boyle&hellip  |  2008-08-14 at 2.29 pm

    I found another case where the same error occurs. If the foreign key is not nullable, then this error is thrown if you try to set the foreign key constraint to ON DELETE SET NULL.

    e.g, using the above example

    ALTER TABLE vik_energy MODIFY COLUMN energy_id energy_id INT NOT NULL;

    ALTER TABLE vik ADD FOREIGN KEY(energy_id) REFERENCES vik_energy(energy_id) ON DELETE SET NOT NULL;

  • 4. Giannis&hellip  |  2009-05-11 at 6.26 am

    Thanks!
    that was really helpfull!

  • 5. Andreas&hellip  |  2009-07-05 at 7.13 pm

    Even though this is an old post, I would also like to add that I had the same error. My solution was that I changed the table to MyISAM instead of previously set innoDB.

    I have a slight memory that I’ve read that innoDB dosen’t support FK. Anyways, hope that this help someone!

  • 6. Confuzed&hellip  |  2009-09-19 at 1.43 am

    Andreas: it did. :)

  • 7. Piers&hellip  |  2010-04-16 at 4.41 pm

    Bah! thanks for the tip… saved my sanity :)

  • 8. Chris&hellip  |  2010-09-26 at 3.41 am

    I’m so glad I found this blog. I’m taking a course in mySQL and ran up against this error. Googled it, came here and problem solved. Thanks! No doubt, I’ll be back.

  • 9. CMR&hellip  |  2010-12-15 at 2.25 am

    I recently ran into this error when trying to create tables through mysql workbench’s outputted script.

    Specifically, the primary key table had a compound primary key (product_id, vendor_id), and the child table had both of these as well, but for some reason MySql Workbench kept outputting the key sequence in the wrong order – (vendor_id, product_id) instead of (product_id, vendor_id). The key sequence must match the order of the primary key index on the parent table.

    A quick edit of the script and it works, but I’m kind of mad at MySQL workbench right now…

Most Recent Posts