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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to create a scatter graph with straight lines 6 26
Download a website to hdd 2 49
Session timeout 5 13
echo time from sql to input type="time" 9 17
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

910 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

18 Experts available now in Live!

Get 1:1 Help Now