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