0

I'm trying to create second foreign key in my table 'tracker_clicks' - image1

The first foreign key is for column 'campaign_id' and it has been created successfully.

But the second one is creating for 'offer_id' column to 'tracker_offers' table - image2

And I get this error - image3

What's wrong?

Denis Zhitnyakov
  • 1
  • 2
  • site is blocked for me. Can you put the error in the question? – Daniel Gale Mar 07 '18 at 13:46
  • Do you already have data in your table which you are trying to add the new foreign key to? – Ryan Wilson Mar 07 '18 at 13:47
  • @DanielGate i've updated image links in post – Denis Zhitnyakov Mar 07 '18 at 13:58
  • @RyanWilson yes, all three tables (parent, for the first FK and for the second one) had data – Denis Zhitnyakov Mar 07 '18 at 14:00
  • @DenisZhitnyakov if your new foreign key column is non-nullable, you will need to add a default value to the column when creating it else it violates foreign key restraints. – Ryan Wilson Mar 07 '18 at 14:01
  • @RyanWilson I've tried to make FK's column null, not null, tried to set default value 0. anyway it doesn't work – Denis Zhitnyakov Mar 07 '18 at 14:07
  • @DenisZhitnyakov Can you post the sql query you are using to try and create the new foreign key? Or are you just trying to do it through the UI? – Ryan Wilson Mar 07 '18 at 14:11
  • @RyanWilson yes, I'm trying to do it through PMA, but it shows all queries. The query is `ALTER TABLE tracker_clicks ADD FOREIGN KEY (offer_id) REFERENCES tracker.tracker_offers(offer_id) ON DELETE RESTRICT ON UPDATE RESTRICT;` And the response is `#1452 - Cannot add or update a child row: a foreign key constraint fails (tracker.#sql-94a_d8d2, CONSTRAINT #sql-94a_d8d2_ibfk_1 FOREIGN KEY (offer_id) REFERENCES tracker_offers (offer_id)) ` – Denis Zhitnyakov Mar 07 '18 at 14:13
  • @DenisZhitnyakov My experience is with SQL Server, so I may not be the best person to try and help you, this link is an SO post dealing with the same error you are getting and is a MySQL question, it may help: (https://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) – Ryan Wilson Mar 07 '18 at 14:18
  • @RyanWilson oh, that's great! The answer is - I shouldn't use id values in FK column that don't exist in child table – Denis Zhitnyakov Mar 07 '18 at 14:27
  • @DenisZhitnyakov Were you able to add the new foreign key column with the help of that post? – Ryan Wilson Mar 07 '18 at 14:33
  • @RyanWilson yes! – Denis Zhitnyakov Mar 07 '18 at 14:52
  • @DenisZhitnyakov Awesome. Glad it helped. – Ryan Wilson Mar 07 '18 at 15:30

0 Answers0