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
518 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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
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 …

839 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