Error code resolution: 1005. Unable to create table '' (errno: 150) Error

Question

I create the following three tables in mysql

• Postmaster
• ADVERTISING
• CANDIDATEMAIN

Here are the create instructions

Postmaster

CREATE TABLE `postmaster` ( `POSTCODE` int(2) NOT NULL DEFAULT '0', `POSTNAME` varchar(250) DEFAULT NULL, PRIMARY KEY (`POSTCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

ADVERTISING

 CREATE TABLE `advertisment` ( `ADVTNO` varchar(35) NOT NULL, `ADVTDATE` date NOT NULL, `POSTCODE` int(2) NOT NULL, `ADVTOPENDATE` date NOT NULL COMMENT 'ADVERTISEMENT OPENING DATE', `ADVTCLOSEDATE` date NOT NULL COMMENT 'ADVERTISEMENT CLOSING DATE', `EDITCLOSEDATE` date NOT NULL COMMENT 'CLOSING DATE FOR EDITING APPLICATION', `LASTREPRINTDATE` date NOT NULL COMMENT 'LAST DATE FOR REPRINTING APPLICATION', `FEESCST` int(4) NOT NULL COMMENT 'FEE FOR SC/ST CATEGORY', `FEESTAFF` int(4) DEFAULT NULL COMMENT 'FEE FOR STAFF ', `FEEOBC` int(4) DEFAULT NULL COMMENT 'FEE FOR OBC CATEGORY', `ADVOCATEEXPERIENCE` int(2) DEFAULT NULL, PRIMARY KEY (`ADVTNO`,`ADVTDATE`,`POSTCODE`), KEY `fk_post` (`POSTCODE`), CONSTRAINT `fk_post` FOREIGN KEY (`POSTCODE`) REFERENCES `postmaster` (`POSTCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Everything is still fine, but when I come to the creation of the CANDIDATEMAIN table, I get an error

Here is the create CANDIDATEMAIN statement

 CREATE TABLE `candidatemain` ( `ADVTNO` varchar(35) NOT NULL DEFAULT '', `ADVTDATE` date NOT NULL DEFAULT '0000-00-00', `POSTCODE` int(2) NOT NULL DEFAULT '0', `REGISTRATIONNO` int(6) NOT NULL DEFAULT '0', `SALUTATION` varchar(10) NOT NULL, `FULLNAME` varchar(90) NOT NULL, `SURNAME` varchar(30) DEFAULT NULL, `NAME` varchar(30) NOT NULL, `LASTNAME` varchar(30) DEFAULT NULL, `LASTUPDATEDIP` varchar(20) NOT NULL DEFAULT '', `LASTUPDATEDDATE` date DEFAULT NULL, `ENTRYDATE` date NOT NULL, PRIMARY KEY (`ADVTNO`,`ADVTDATE`,`POSTCODE`,`REGISTRATIONNO`), KEY `FK_ADVT` (`ADVTNO`, `ADVTDATE`,`POSTCODE`) , CONSTRAINT `FK_ADVT` FOREIGN KEY (`ADVTNO`, `ADVTDATE`,`POSTCODE`) REFERENCES `advertisment` (`ADVTNO`, `ADVTDATE`,`POSTCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

The error I am getting is:

 Error Code: 1005. Can't create table 'dbName.candidatemain' (errno: 150) 

What is the reason for this error?

MYSql Verion: mysql Ver 14.14 Distribution 5.1.53 for Win64

Answer 1

Create advertisment table with default encoding as utf8 ;

Columns

latin1 is not equivalent to utf8 columns. Therefore, a foreign key constraint cannot be formed correctly. Therefore, error 1005 (ER_CANT_CREATE_TABLE) .

Here is my mysql log.

 mysql> CREATE TABLE `advertisment` ( -> `ADVTNO` varchar(35) NOT NULL, -> `ADVTDATE` date NOT NULL, -> `POSTCODE` int(2) NOT NULL, -> `ADVTOPENDATE` date NOT NULL COMMENT 'ADVERTISEMENT OPENING DATE', -> `ADVTCLOSEDATE` date NOT NULL COMMENT 'ADVERTISEMENT CLOSING DATE', -> `EDITCLOSEDATE` date NOT NULL COMMENT 'CLOSING DATE FOR EDITING APPLICATION', -> `LASTREPRINTDATE` date NOT NULL COMMENT 'LAST DATE FOR REPRINTING APPLICATION', -> `FEESCST` int(4) NOT NULL COMMENT 'FEE FOR SC/ST CATEGORY', -> `FEESTAFF` int(4) DEFAULT NULL COMMENT 'FEE FOR STAFF ', -> `FEEOBC` int(4) DEFAULT NULL COMMENT 'FEE FOR OBC CATEGORY', -> `ADVOCATEEXPERIENCE` int(2) DEFAULT NULL, -> PRIMARY KEY (`ADVTNO`,`ADVTDATE`,`POSTCODE`), -> KEY `fk_post` (`POSTCODE`), -> CONSTRAINT `fk_post` FOREIGN KEY (`POSTCODE`) REFERENCES `postmaster` (`POSTCODE`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- <== This little change make it work. Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE `candidatemain` ( -> -> `ADVTNO` varchar(35) NOT NULL DEFAULT '', -> `ADVTDATE` date NOT NULL DEFAULT '0000-00-00', -> `POSTCODE` int(2) NOT NULL DEFAULT '0', -> `REGISTRATIONNO` int(6) NOT NULL DEFAULT '0', -> `SALUTATION` varchar(10) NOT NULL, -> `FULLNAME` varchar(90) NOT NULL, -> `SURNAME` varchar(30) DEFAULT NULL, -> `NAME` varchar(30) NOT NULL, -> `LASTNAME` varchar(30) DEFAULT NULL, -> `LASTUPDATEDIP` varchar(20) NOT NULL DEFAULT '', -> `LASTUPDATEDDATE` date DEFAULT NULL, -> `ENTRYDATE` date NOT NULL, -> PRIMARY KEY (`ADVTNO`,`ADVTDATE`,`POSTCODE`,`REGISTRATIONNO`), -> KEY `FK_ADVT` (`ADVTNO`, `ADVTDATE`,`POSTCODE`) , -> CONSTRAINT `FK_ADVT` FOREIGN KEY (`ADVTNO`, `ADVTDATE`,`POSTCODE`) REFERENCES `advertisment ` (`ADVTNO`, `ADVTDATE`,`POSTCODE`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.12 sec) 

Answer 2

Your FOREIGN KEY CONSTRAINT table has a different encoding for the candidatemain and advertisment tables, this causes an error, even if they have the same encoding.