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,

  • Can you shut these databases down for a (shortish) period (separately) or the entire server for a time - exact time I can't tell (not enough info about size of db or no. of tables). – Vérace Aug 27 '14 at 15:44
  • I suppose that if needed I could, yes. At worst I can always do it at night. – Ulrar Aug 27 '14 at 15:56
  • I'm not a wizard but I know you can SET FOREIGN_KEY_CHECKS=0; which should turn off any constraint checking. Turn it back on with =1. I'm not sure what will happen if you do this with bad keys and turn it back on though... – LainIwakura Jul 17 '15 at 14:11

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.

| improve this answer | |
  • Okay, thanks a lot. The problem being that it would require me to write individual requests for each table foreign key, that's hundred of them. I guess I might be able to write a script to generate those and just check if they seem valid, that'd be less work – Ulrar Aug 28 '14 at 7:38
  • You said that they were all duplicates of the "same original db". I took that to mean that the table names were the same - you should (if I've understood you correctly) be able to run the same script for each of your 20 databases, just change the database name (i.e. "yourdb" in the mysql client command that I gave you) - assuming that the table names are the same. You will have to do the work for your 100 tables, but that's a one-off (no pain, no gain! :-) ) – Vérace Aug 28 '14 at 7:44
  • Yes, all the databases have the same structure, but there is a lot of tables in them so I still have to write the queries for each of those tables, which will take days, even if after I can just run the same script 20 times. We already have a script to update all the databases so that's not an issue. I might be able to get a developer to do that, I don't know if I should delete from the parent or the child, that's not my app. Thanks anyway – Ulrar Aug 28 '14 at 9:39
  • The delete barren parents query will only affect parents who have no children. The delete orphaned records query will only delete children who have no parents - they can be run in either order, since they don't affect each other. Be sure and hang on to the records in the _bak tables. You may well find that users/clients will want to recuperate any data from those in the future. You could also be looking at some manual tidying up on an ad-hoc basis until the system beds in. The lesson here is no matter what the app does, ALWAYS enforce RI (Referential Integrity) in the database. Good luck! – Vérace Aug 28 '14 at 9:45
  • Well, I wasn't even working here when the mistake was made, but that's still a good thing to know. I was thinking of another way, would it be possible to export only the data of the tables, recreate the structure with the constraints as it should have been done before, and reimport all the data ? Would it not achieve the same result ? – Ulrar Aug 28 '14 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.