在外键约束中添加复合键

Question

我正在尝试添加一个新的外键约束和一个索引名称

ALTER TABLE lng02_rpt_b_calvedets_msel_calfsire_secbreed ADD CONSTRAINT fklng021_lng02_rpt_b_calvedets_msel_calfsire_secbreed
FOREIGN KEY (hh_id,rpt_b_calvedets_rowid) REFERENCES lng02_rpt_b_calvedets(hh_id,rpt_b_calvedets_rowid);

我的查询的输出错误是

Error Code: 1005. Can't create table `adggeth`.`#sql-e24_df1437` (errno: 150 "Foreign key constraint is incorrectly formed")    0.0057 sec

我的查询只有在我不包括两个外键的情况下才有效，如下

ALTER TABLE lng02_rpt_b_calvedets_msel_calfsire_secbreed ADD CONSTRAINT fklng021_lng02_rpt_b_calvedets_msel_calfsire_secbreed
FOREIGN KEY (hh_id) REFERENCES lng02_rpt_b_calvedets(hh_id);

要求的表格定义

CREATE TABLE `lng02_rpt_b_calvedets` (
`hh_id` varchar(10) DEFAULT NULL COMMENT 'Farmer',
`damid` varchar(80) NOT NULL COMMENT 'Tag ID of the mother',
`calvdatealv` date NOT NULL COMMENT 'Calving date',
-- i have removed most columns for you to get the picture 
`rpt_b_calvedets_rowid` int(10) DEFAULT NULL,
-- i have removed most columns for you to get the picture 
`calfstrawid` varchar(15) DEFAULT NULL,
PRIMARY KEY (`damid`,`calvdatealv`),
UNIQUE KEY `nosameanimalid` (`animalid`),
UNIQUE KEY `nosamecalftagid` (`tagid`),
KEY `fk5_lng02_rpt_b_calvedets_reg04_rpt_animreg` (`damid`),
KEY `fk6_lng02_rpt_b_calvedets_lng02_lkpsex` (`sex`),
KEY `fk7_lng02_rpt_b_calvedets_lng02_lkpcalvtype` (`calvtype`),
KEY `fk8_lng02_rpt_b_calvedets_lng02_lkpeasecalv` (`easecalv`),
KEY `fk9_lng02_rpt_b_calvedets_reg04_lkpsiretype` (`siretype`),
KEY `fk10_lng02_rpt_b_calvedets_reg04_rpt_animreg` (`siretag`),
KEY `fk11_lng02_rpt_b_calvedets_reg02_lkpe2_catlebreed` (`sirebreed`),
KEY `fk12_lng02_rpt_b_calvedets_reg03_lkpownbull_breedcomp` (`sirecomp`),
KEY `fk13_lng02_rpt_b_calvedets_reg02_lkpe2_catlebreed` (`strawbreed`),
KEY `fk14_lng02_rpt_b_calvedets_reg03_lkpownbull_breedcomp` (`strawcomp`),
KEY `fk15_lng02_rpt_b_calvedets_lng02_lkpfeedmth` (`feedmth`),
KEY `fk16_lng02_rpt_b_calvedets_lng02_lkpintuse` (`intuse`),
KEY `fk17_lng02_rpt_b_calvedets_lng02_lkpwhydead` (`whydead`),
KEY `longDIDX1` (`siretag`),
KEY `fk13_lng02_rpt_b_calvedets_lkpbirthtyp` (`birthtyp`),
KEY `fklng02_lng02_rpt_b_calvedets` (`hh_id`),
CONSTRAINT `fk10_lng02_rpt_b_calvedets_reg04_rpt_animreg` FOREIGN KEY (`siretag`) REFERENCES `reg04_rpt_animreg` (`animalid`) ON UPDATE NO ACTION,
CONSTRAINT `fk11_lng02_rpt_b_calvedets_reg02_lkpe2_catlebreed` FOREIGN KEY (`sirebreed`) REFERENCES `reg02_lkpe2_catlebreed` (`e2_catlebreed_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk12_lng02_rpt_b_calvedets_reg03_lkpownbull_breedcomp` FOREIGN KEY (`sirecomp`) REFERENCES `reg03_lkpownbull_breedcomp` (`ownbull_breedcomp_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk13_lng02_rpt_b_calvedets_lkpbirthtyp` FOREIGN KEY (`birthtyp`) REFERENCES `lng02_lkpbirthtyp` (`birthtyp_cod`),
CONSTRAINT `fk13_lng02_rpt_b_calvedets_reg02_lkpe2_catlebreed` FOREIGN KEY (`strawbreed`) REFERENCES `reg02_lkpe2_catlebreed` (`e2_catlebreed_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk14_lng02_rpt_b_calvedets_reg03_lkpownbull_breedcomp` FOREIGN KEY (`strawcomp`) REFERENCES `reg03_lkpownbull_breedcomp` (`ownbull_breedcomp_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk15_lng02_rpt_b_calvedets_lng02_lkpfeedmth` FOREIGN KEY (`feedmth`) REFERENCES `lng02_lkpfeedmth` (`feedmth_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk16_lng02_rpt_b_calvedets_lng02_lkpintuse` FOREIGN KEY (`intuse`) REFERENCES `lng02_lkpintuse` (`intuse_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk6_lng02_rpt_b_calvedets_lng02_lkpsex` FOREIGN KEY (`sex`) REFERENCES `lng02_lkpsex` (`sex_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk7_lng02_rpt_b_calvedets_lng02_lkpcalvtype` FOREIGN KEY (`calvtype`) REFERENCES `lng02_lkpcalvtype` (`calvtype_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk8_lng02_rpt_b_calvedets_lng02_lkpeasecalv` FOREIGN KEY (`easecalv`) REFERENCES `lng02_lkpeasecalv` (`easecalv_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fk9_lng02_rpt_b_calvedets_reg04_lkpsiretype` FOREIGN KEY (`siretype`) REFERENCES `reg04_lkpsiretype` (`siretype_cod`) ON UPDATE NO ACTION,
CONSTRAINT `fklng02_lng02_rpt_b_calvedets` FOREIGN KEY (`hh_id`) REFERENCES `lng01_maininfo` (`hh_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Registry for each calving';

Answer 1

要创建复合外键，甚至是单个字段外键，被引用的表必须在外键引用的字段上有索引。

示例：如果B用(f1，f2(引用A，则A必须在(f1，f2(上有索引。(f1，f2，fX(上的索引也就足够了；但是(f2，f1(不会，也不会分离(f1(和(f2(上的索引。