#1452 - Cannot add or update a child row: a foreign key constraint fails?

Databases
1

I have a shopping cart and am trying to do a fresh install of it while preserving the existing data in the database. So I installed the new version of the cart in a separate folder with a new database. I then followed the instructions below:

  1. select the new database
  2. go to tab “structure”
  3. check all tables
  4. “with selected” -> empty

Rather that exporting and importing it could be worth a try to copy the old data into the new database
(it assumes they run on the same server)

  1. select the old database
  2. go to tab “operations”
  3. copy database to -> “livecart_new” (name of the new database you have already created)
    • select “data only”
      It should now copy the old data into the new database.

I was able to successfully empty all the tables of the fresh install, but ran into this when I tried to copy the data from the old database into the new empty database:

[B]Error
SQL query: DocumentationEdit

INSERT INTO robertco_LC131.AccessControlAssociation
SELECT *
FROM robertco_LC.AccessControlAssociation

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (robertco_LC131/AccessControlAssociation, CONSTRAINT UserGroup_AccessControlAssociation FOREIGN KEY (userGroupID) REFERENCES UserGroup (ID) ON DELETE CASCADE ON UPDATE CASCADE) [/B]

Can anyone assist me with this error? How come I simply can’t copy data from old to new?

2

First copy the data in the UserGroup table :slight_smile:

3

I see, so it can’t just do a bulk copy from one database to the other? I have to figure out how to copy just the data then from that table in the old to the same in the new database…

4

If you have foreign key constraints, the tables have to be loaded in a certain order. Otherwise, the constraint will fail since the values are not present yet in the other table.

5

I understand the meaning of the error now. Is there a simple way, or actual command that will copy the data from the tables in the order they need to go into the new database?