5

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;
  • Ah, +1 for solving your own problem !!! – RolandoMySQLDBA Sep 6 '11 at 15:00
  • 3
    @Ben, if you would be so kind as to post your answer as an answer proper, and mark it accepted, you'll get upvotes (higher rep) and it'll be more clear what transpired here. – jcolebrand Sep 6 '11 at 18:08
1

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"...

  • 1
    NULL and NOT NULL are compatible. INT UNSIGNED and INT are not. – ypercubeᵀᴹ Jan 14 '13 at 12:26
0

Yes, I had this problem with an unsigned int parent primary key that is being referenced by a child with an int foreign key. Unsigned int with int will throw a 150 error no.

  • I realize you don't have the required rep yet, but this should be a comment, not an answer. – Max Vernon Oct 26 '13 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.