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

6 Comments Add your own

  • 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. :)

Leave a Comment

Required

Required, hidden

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed

Most Recent Posts