CODEWITHSUNDEEP
sqlsqlyog
Nia
Nia

Reputation: 151

Error Code: 1005 - Can't create table (errno: 150)

I apologize for asking question related to this topic, I know this topic has been discussed on few other posts and its kind of a duplicate but the posts I been through did not help me in fixing my issue.

I have the following SQL code to add two tables:

CREATE TABLE IF NOT EXISTS `opa`.`iddocumenttype`(  
`iddocumenttypeid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
 PRIMARY KEY(`iddocumenttypeid`),
 KEY (`iddocumenttypeid`)
) ENGINE=INNODB CHARSET=utf8;

and

 CREATE TABLE IF NOT EXISTS `opa`.`identificationdocumentdetails`(  
`identificationdocumentdetailsid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`IDdocumenttypeid` INT(11) UNSIGNED NOT NULL,
`IDdocumentnumber` VARCHAR(128),
`name` VARCHAR(200) COMMENT 'Exact name as on idDoc?',
`dateofissue` DATE CHECK (dateofissue < CURRENT_DATE()),
`placeofissue` VARCHAR(128),
`validtill` DATE CHECK (validtill > CURRENT_DATE()),
 PRIMARY KEY (`identificationdocumentdetailsid`),
 CONSTRAINT `FK_identificationdocumentdetails_iddocumenttype` FOREIGN KEY (`IDdocumenttypeid`) REFERENCES `opa`.`iddocumenttype`(`iddocumenttypeid`)) ENGINE=INNODB CHARSET=utf8;

Now, when I run the query to create the second table which is identificationdocumentdetails , I get the following error:

Error Code: 1005
Can't create table 'opa.identificationdocumentdetails' (errno: 150)

I dont understand why its happening, I am sure some thing has to do with CONSTRAINT line because when I remove this line:

CONSTRAINT `FK_identificationdocumentdetails_iddocumenttype` FOREIGN KEY (`IDdocumenttypeid`) REFERENCES `opa`.`iddocumenttype`(`iddocumenttypeid`)

the store procedure works fine, but I think I am missing whats going wrong here, Can some one please point out to me what am I not seeing here....

Upvotes: 2

Views: 1520

Answers (1)

Mihai
Mihai

Reputation: 26784

`IDdocumenttypeid` INT(11)

in identificationdocumentdetails table need to be the exactly same type as the column referenced,which is

`iddocumenttypeid` INT(11) UNSIGNED NOT NULL

http://sqlfiddle.com/#!9/66b6d

Upvotes: 4

Related Questions