MySQL, foreign key, can’t create table error 150

foreign keyMySQL

UPDATE: Sorry, it was because i forgot to add UNSIGNED attribute to the lang_id column.

Original:
I'm trying to create a table with foreign key in MySQL.

I get this error: #1005 – Can't create table 'xy.trans' (errno: 150)

Any ideas whats wrong with the following create statement?

CREATE  TABLE IF NOT EXISTS `lang` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` CHAR(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;


CREATE  TABLE IF NOT EXISTS `trans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `something` TEXT NOT NULL,
  `lang_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `index_trans_lang` (`lang_id` ASC),
  CONSTRAINT `fk_trans_lang`
    FOREIGN KEY (`lang_id` )
    REFERENCES `lang` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE = InnoDB;

Best Answer

I've had a similar problem with this, and I finally resolved it by checking the field types of the two fields that were being referenced - they have to be exactly the same type - all the way down to the "Not NUll" and "Unsigned" settings...

In your original post, the "lang" table has the "id" field defined as "INT UNSIGNED NOT NULL", whereas the "trans" table has the "lang_id" field defined as "INT NULL"...

Related Question