1

I am trying to add a constraint in my tables and I keep getting the following

11:11:55 ALTER TABLE adggtnz.lng01_rpt_animrec ADD CONSTRAINT 'fk20_lng01_rpt_animrec_reg04_rpt_animreg' FOREIGN KEY ('animalid') REFERENCES 'reg04_rpt_animreg' ('animalid')

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails ('adggtnz'.'#sql-74e_2219446', CONSTRAINT 'fk20_lng01_rpt_animrec_reg04_rpt_animreg' FOREIGN KEY ('animalid') REFERENCES 'reg04_rpt_animreg' ('animalid')) 0.077 sec

I have used the following query to add my constraint

ALTER TABLE adggtnz.lng01_rpt_animrec 
ADD CONSTRAINT 'fk20_lng01_rpt_animrec_reg04_rpt_animreg' 
FOREIGN KEY ('animalid') REFERENCES 'reg04_rpt_animreg' ('animalid');

How can I ran the following query successfully and avoid an error as a result example of lng01_rpt_animrec:
column name

animalid,milkdate

'0065ae2f-62b2-45dd-b0c4-e6479e57e4c8', NULL
'0065ae2f-62b2-45dd-b0c4-e6479e57e4c8', '2018-05-29'
'0065ae2f-62b2-45dd-b0c4-e6479e57e4c8', NULL
'0065ae2f-62b2-45dd-b0c4-e6479e57e4c8', '2017-09-22'
'02db813b-c2a4-4ce1-802e-51f48e1d93ba', '2018-05-07'
'02db813b-c2a4-4ce1-802e-51f48e1d93ba', '2018-06-29'
'04f2d070-6336-4426-8e13-733872ee4bb3', '2018-01-02'
'0555e8ce-ba91-4a00-8a1d-133ff849ef87', '2017-10-02'
'05ab7837-005d-4494-8c32-f290cc83c1af', '2018-06-16'
'05ab7837-005d-4494-8c32-f290cc83c1af', NULL

example of reg04_animreg:
columns:

hh_id,regdate,animalid

'0', '2017-02-13', 'TZN000362738061'
'0', '2017-02-13', 'TZN000362738062'
'0', '2017-02-13', 'TZN000362738074'
'00', '2018-01-20', 'TZN000404007294'
'00000', '2017-06-14', 'TZN000404001178'
'00000', '2017-06-14', 'TZN000404001179'
'000000', '2017-07-21', 'TZN000403255840'
'0000000000_DUP1', '2017-05-31', 'TZN000365694884'
'0000000000_DUP2', '2017-06-06', 'TZN123456782234'
'0099887764', '2017-03-19', '03b704e2-bcdd-46e0-85e8-f38f21f16507'
cezar
  • 11,616
  • 6
  • 48
  • 84
Mirieri Mogaka
  • 517
  • 4
  • 23
  • Are your tables empty, when you trying to add the constraint? – R. García Jul 25 '18 at 08:37
  • my tables have data – Mirieri Mogaka Jul 25 '18 at 08:39
  • 3
    If your tables have data, make sure that all children have parents. Is there an index on the column in the destination table? – fancyPants Jul 25 '18 at 08:39
  • @MirieriMogaka Maybe, it could be the problem... Explain the problem with more information (Columns, Example rows, Table Estructure)... – R. García Jul 25 '18 at 08:42
  • the foreign key for adggtnz.lng01_rpt_animrec is animalid the primary key for reg04_rpt_animreg is animalid – Mirieri Mogaka Jul 25 '18 at 08:46
  • Are the datatypes for `animalid` the same in both tables? Do both tables use the same collation? – Barmar Jul 25 '18 at 08:47
  • 1
    You need to make sure all data present in animalid column of `lng01_rpt_animrec` is present in animalid column of main table `reg04_rpt_animreg`. – skelwa Jul 25 '18 at 08:48
  • The inserts to lng01_rpt_animrec in your sample data are not going to succeed because there are no matching animalid in reg04_animreg. If you want to push them in anyway you could set foreign key checks off temporarily see https://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql – P.Salmon Jul 25 '18 at 10:16

0 Answers0