I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is not actually conducive to a restoration of the database(including relationships) on my end should the need arise.

Upon review of the structure I see that there are no Foreign Keys in the tables, but I see there are KEYS(indexes) that appear to correspond with what the foreign keys should be.

When performing a MySQL dump, does setting FOREIGN_KEY_CHECKS=0 mean that there are no relationships/foreign keys included in the dump?

Is it true that when restoring a MySQL dump that had FOREIGN_KEY_CHECKS=0 at the time of the dump , the foreign key relationships will not be present?

Is there a way to create foreign key relationships from the existing KEYS?

  • 1
    The dump starts with SET FOREIGN_KEY_CHECKS=0. Isn't there a line at the end of the script that resets back the foreign key check settting? If I remember well, that is the default output of mysqldump. It's only used so the foreign key constraints are not enforced during the mysql restore of the dump, to make it faster. If the CREATE TABLE statatements have no FOREIGN KEY constraints defined, then the dumped database has no foreign keys defined. – ypercubeᵀᴹ Nov 6 '15 at 21:06

That is a default option in mysqldump. As the name states it only disables the foreign key check, which is necessary. If you don't disable the check, you'll almost certainly run into foreign keys errors, unless the restore, coincidentally, honors the parent/child relationships in the sequence that it restored the tables in. Extremely unlikely. Mysqldump doesn't account for this.

Look at it this way. If your dump restore tries to restore a child table before its parent, it will see the rows as orphan records and the check will give an error.

As ypercube said. The dump file will contain a set command at the end to turn the check back on. It's also a session based variable.

To address your concern. No. The set command does not remove foreign keys from your tables.

|improve this answer|||||
  • The only way to derive the original, if existent, foreign key scheme from the dump might be by looking at the name of the keys you see. Sometimes the names are descriptive. Seems far fetched and messy though. Better to check out the schema the dump was made of. – naschoff Nov 6 '15 at 22:59

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.