Adding foreign keys constraint on databases used

Question

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,

Answer 1

OK - following my comment, here is what I would do.

Before shutting down the server or individual database.

1. Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar).

2. Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything).

3. (removed during edit)

4. Prepare an SQL script file (salvage.sql? :-))

   Now, the question is are you removing orphan records (i.e. child records that have no corresponding record in the parent table)? Or are you deleting barren parents (i.e. parents with no child records)? Below will do both.

   INSERT INTO parent_bak 
   SELECT * FROM parent 
   WHERE parent.primary_key NOT IN 
   (
       SELECT child.foreign_key 
       FROM child
   ); -- backup barren parents
   
   INSERT INTO child_bak 
   SELECT * FROM child 
   WHERE child.foreign_key 
   NOT IN 
   (
       SELECT parent.primary_key 
       FROM parent
   ); -- backup orphan records
   
   DELETE FROM parent 
   WHERE parent.primary_key 
   NOT IN 
   (
       SELECT child.foreign_key 
       FROM child
   ); -- remove invalid records from your original tables.
   
   DELETE FROM child 
   WHERE child.foreign_key NOT IN 
   (
       SELECT parent.primary_key 
       FROM parent
   ); -- remove invalid records from your original tables.
   

   Add your FOREIGN KEY constraints.

5. Stop all clients connecting to the server or database (so updates can't occur while running the copy into _bak tables (invalid data) and the deletes.

6. Run:

   mysql -h host -u username -ppassword yourdb < salvage.sql
   

   ...for each of your servers. For your 20 databases, you could write a script for all 20 and run that.

---

The time this will take will depend on the size of your databases and the number of tables. Warning - there may be typos in this post - I didn't run the scripts on a server, but you should get the idea.

Test on at least one database before running on your live server.

[EDIT] Forgot - add your FOREIGN KEY constraints at the end of salvage.sql also.