Adding a composite key in a foreign key constraint

Question

i am trying to add a new foreign key constraint and an index name

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);

The output error for my query is

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

My query only works when i do not include two foreign keys as follows

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);

table definations as requested

 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';

Please edit your question to include the table definition of `lng02_rpt_b_calvedets`, including the indices of that table. — Progman, Nov 16 '18 at 17:47
You might like to read the checklist for foreign keys: https://stackoverflow.com/a/4673775/20860 — Bill Karwin, Nov 16 '18 at 17:56
@MirieriMogaka Your table has the columns "hh_id" and "calfstrawid", but your `PRIMARY KEY` is referencing the columns "damid" and "calvdatealv". Please edit your question to include the table definition of `lng02_rpt_b_calvedets` including the indices of that table. Do NOT change/edit the output of the table definition. — Progman, Nov 16 '18 at 18:01

Uueerdo · Answer 1 · 2018-11-16T17:56:24.243

2

To create a composite foreign key, or even a single field one, the referenced table must have an index on the fields the foreign key references.

Example: If B references A with (f1, f2), then A must have an index on (f1, f2). An index on (f1, f2, fX) will also suffice; but (f2, f1) will not, nor will separate indexes on (f1) and (f2).

edited Nov 16 '18 at 17:56

answered Nov 16 '18 at 17:54

Uueerdo

15,723
1
16
21

please edit my query so that i can be able to apply your surgesstion – Mirieri Mogaka Nov 16 '18 at 17:56
1

Your query looks fine, at a casual glance at least; you need to alter the target table to have an appropriate index. – Uueerdo Nov 16 '18 at 17:57

Adding a composite key in a foreign key constraint

1 Answers1