我正在mysql 中创建以下三个表
- 邮政局长
- 广告
- 候选人
以下是创建语句
邮政局长
CREATE TABLE `postmaster` (
`POSTCODE` int(2) NOT NULL DEFAULT '0',
`POSTNAME` varchar(250) DEFAULT NULL,
PRIMARY KEY (`POSTCODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
广告
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
到这里为止一切都很好,但当我创建CANDIDATEMAIN
表时,我会得到错误
这是CANDIDATEMAIN的创建语句
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
我得到的错误是:
Error Code: 1005. Can't create table 'dbName.candidatemain' (errno: 150)
出现此错误的原因可能是什么?
MYSql Verion:MYSql版本14.14 Distrib 5.1.53,适用于Win64
创建默认字符集为utf8
的advertisment
表
latin1列不等同于utf8的列。因此,外键约束不能正确形成。因此出现CCD_ 4错误。
这是我的mysql日志。
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)
表candidatemain
和advertisment
的FOREIGN KEY CONSTRAINT
具有不同的字符集,这会导致错误,让它们具有相同的字符集。