Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Need to relate two tables via index and foreign key, but the other table already has data, how to resolve?

Getting: #1452 - cannot add or update a child row a foreign key constraint fails mysql

Need to relate two tables via index and foreign key, but the other table already has data so I'm getting the above error, how to resolve?

And could someone explain why it's a problem that there's data there already?
0
LB1234
Asked:
LB1234
  • 2
2 Solutions
 
Chris StanyonCommented:
The whole purpose of linking tables like this is to maintain referential integrity. If you have for example a customer and order table with linked together, it would make no sense to have an order that doesn't have a corresponding customer, and if you tried to add an order without a matching customer, you would get an error.

Sounds like you have the same problem. The 2 tables are separate at the moment so there is no relationship between the two. When you try and add the constraint, you are telling the system that for every child record, there must be a parent record, and it sounds like that's not the case.

Ideally, you set these things up before adding data, but in your case, you may have to check that the records have a match so you add the constraint.
0
 
LB1234Author Commented:
All right I'm testing something out.  I created a small database called cats, with two tables: cats and kennels.

I have a cats_id autoincrementing in the cats table and a cats_id in the kennels table.  A relationship has been created.  When I enter a new cat in the cats table, it creates an autoincremented int in the cats table.  In the kennel field, I would "1" to associate that cat with the kennel it was being kept in correct?  If so, what if I had hundreds of cats?  I'd have to look up the cats_id for each one before entering the number into the kennel?  That can't be the case can it?!
0
 
Chris StanyonCommented:
You're on the right track.

As for looking up a Cat ID to put in the kennel table, there are several ways of doing it, depending on your requirements.

You could write code in your PHP script that grab's the relevant Cat ID to use when you insert a new Kennel record.

If your Kennel table will always have a matching record for every cat, then you can use SQL triggers. Basically this is a function that would fire when you inserted a new Cat, and automatically create a matching kennel Record.

If you're entering data into mySQL manually, then some SQL Clients (I know SQLYog does) will make the foreign key field a dropdown, listing the records from the primary table - still not ideal but saves you having to manually look up the records.

This model isn't specific to mySQL - is just general database principles, and will rely on your own programming logic.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now