Q

Nullifying foreign key statements when loading a test dev MySQL database

MySQL expert Mike Hillyer gives the shutoff command for foreign key checks when loading data.

Is there a way to write a SQL statement that will override (set to null) all foreign keys? I want to use this when loading a test development MySQL database.

When loading test data, you can issue the following statement to shut off foreign key checks in your session:

SET FOREIGN_KEY_CHECKS=0;

When you are finished loading your data, issue the following statement to turn foreign key checking back on:

SET FOREIGN_KEY_CHECKS=1;

Remember to have all foreign key requirements met before turning foreign key checks back on.

This was first published in April 2006

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close