We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all the databases on that server are missing them too. Now, these databases have been used for some time now, and there is data in them. We noticed that in the export done from phpmyadmin, the constraint are at the end, so I did an export from a valid database and I now have a few hundreds lines of ALTER TABLE to add the constraints, the problem being that if I start applying that to our databases, MySQL will reject it because the keys that should have been deleted by the constraint weren't.
Is there any way around that ? That's a lot of databases and a lot of tables, it's just not possible to go in all of them and delete the data manually, and since those constraints should have been here from the start, I feel that all the data that would prevent those keys from being added could be dropped to get back to the "normal" state, am I wrong ?
I'm not a database administrator, so I have no idea if that's even possible. I've been told that ALTER IGNORE TABLE works like that for UNIQUE, would it work for constraints too ? Or is there a way to tell MySQL to ignore the errors when adding the constraints and then DELETE all the unmatching entries ?
Thanks a lot,