Copy Database To Different Host/db & Foreign Keys Havoc!

[Splash!]

This is a simplified example of the problem and circumstances surrounding it:

I have a database which contains products in a `products` table, and categories to which they belong in a `categories` table. As you might imagine, each product has an id, and each category has an id. The products also have a categoryid, which is a foreign key for categories.id.

Attempting to copy this database to a different host/db fails when I try to copy the entire thing. The only way to make it work seems to be to make it copy the products table (and data) first, and once that has completed, copy the categories table & data. If I do it in the other order (which is what I assume it is trying to do when both tables are selected), it fails.

I assume its the key its got a problem with. Any fix or workaround other than having to copy it like I describe above? Its not a massive problem for a relatively simple DB like this one, but as you could imagine, if the DB were much more complex, it could become nightmarish.

[peterlaursen]

PROGRAM VERSION PLEASE !!! Did you try latest available version? .. how often will we have to ask those questions ???

What does 'fails' mean? a SPECIFIC description please, EXACT error message ?!?!

The solution for such problems with Foreign Keys ALWAYS is to “SET FOREIGN_KEY_CHECKS = 0”. This should be done by the program. If it does not it is a bug! … So again: PROGRAM VERSION PLEASE !!! Did you try latest available version?

[Splash!]

Program version: SQLyog Community Edition 6.04 (running on Win2000)

However, I am doing it again in order to reproduce the error message for you, and it isnt doing it this time. I will play around and post again if I can figure out what went wrong. If not, I guess its resolved. 😕

Maybe it had something to do with the fact that the first time I tried it, I copied just the structure over, and then did it again with the data as well. Seems unlikely this would hurt it, but it sure isnt failing this time.

Thanks anyway, and I apologise for posting without the program version. 🙄 I really should know better. 🙂

[peterlaursen]

It is true of course that copying (or restoring from a backup or sync'ing) a populated 'child' table would fail because of the foreign key CONSTRAINT if not the 'parent' table was populated in advance. That is why the SET FOREIGN_KEY_CHECKS command was invented.

Alternatively you would have to populate the tables in a certain order, and the server has no way to generate that order itself. It would involve a lot of application code to find out that order (and in the (rare) situation where references are circular it would simply not be possible at all).

I cannot explain what happened this time when you had the error. Looks like the server 'forgot' or 'ignored' the SET FOREIGN_KEY_CHECKS command.

And yes .. If you can reproduce it then please report details!

[Author]

Posts