Solved

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

Posted on 2014-01-28
3
514 Views
Last Modified: 2014-01-28
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
Comment
Question by:LB1234
  • 2
3 Comments
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:LB1234
Comment Utility
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
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now