Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
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
add comment

2 Answers

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

share|improve this answer
1  
NULL and NOT NULL are compatible. INT UNSIGNED and INT are not. –  ypercube Jan 14 '13 at 12:26
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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