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
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 total points
ID: 39815358
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
ID: 39815546
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 43

Accepted Solution

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to create an extensible mechanism for linked drop downs.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

726 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