{"id":8334,"date":"2019-09-02T18:36:03","date_gmt":"2019-09-03T01:36:03","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8334"},"modified":"2023-12-29T21:09:40","modified_gmt":"2023-12-30T04:09:40","slug":"mysql-disable-foreign-key-checks","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-disable-foreign-key-checks\/","title":{"rendered":"MySQL Disable Foreign Key Checks"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to disable foreign key constraint checks in MySQL.<\/p>\n\n\n\n

Sometimes, it is very useful to disable foreign key<\/a> checks. For example, you can load data to the parent and child tables in any order with the foreign key constraint check disabled. <\/p>\n\n\n\n

If you don’t disable foreign key checks, you have to load data into the parent tables first and then the child tables in sequence, which can be tedious.<\/p>\n\n\n\n

Another scenario in which you want to disable the foreign key check is when you want to drop a table<\/a>. Unless you disable the foreign key checks, you cannot drop a table<\/a> referenced by a foreign key constraint.<\/p>\n\n\n\n

To disable foreign key checks, you set the foreign_key_checks<\/code> variable to zero as follows:<\/p>\n\n\n

SET<\/span> foreign_key_checks = 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To enable the foreign key constraint check, you set the value of the foreign_key_checks<\/code> to 1:<\/p>\n\n\n
SET<\/span> foreign_key_checks = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that setting foreign_key_checks<\/code> to 1 does not trigger any validation of the existing table data. In other words, MySQL will not verify the consistency of the data that was added during the foreign key check disabled.<\/p>\n\n\n\n
Disable foreign key check example<\/h2>\n\n\n\n
First, create a new table<\/a> named countries<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> countries(\n    country_id INT<\/span> AUTO_INCREMENT,\n    country_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(country_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> cities(\n    city_id INT<\/span> AUTO_INCREMENT,\n    city_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    country_id INT<\/span> NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span>(city_id),\n    FOREIGN<\/span> KEY<\/span>(country_id) \n\t\tREFERENCES<\/span> countries(country_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, insert a new row<\/a> into the cities<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> cities(city_name, country_id)\nVALUES<\/span>('New York'<\/span>,1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error:<\/p>\n\n\n
Error<\/span> Code: 1452.<\/span> Cannot add or update a child row: a foreign key constraint fails (`classicmodels`<\/span>.`cities`<\/span>, CONSTRAINT `cities_ibfk_1`<\/span> FOREIGN KEY (`country_id`<\/span>) REFERENCES `countries`<\/span> (`country_id`<\/span>))<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, disable foreign key checks:<\/p>\n\n\n
SET<\/span> foreign_key_checks = 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, insert a new row into the cities<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> cities(city_name, country_id)\nVALUES<\/span>('New York'<\/span>,1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This time the INSERT<\/code> statement executed successfully due to the foreign key check being disabled.<\/p>\n\n\n\n
SELECT<\/span> * FROM<\/span> cities;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Sixth, re-enable foreign key constraint check:<\/p>\n\n\n
SET<\/span> foreign_key_checks = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When the foreign key checks were re-enabled, MySQL did not re-validate data in the table. However, it won’t allow you to insert or update data that violates the foreign key constraint.<\/p>\n\n\n\n
Finally, insert a row into the countries<\/code> table whose value in the column country_id<\/code> is 1 to make the data consistent in both tables:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> countries(country_id, country_name)\nVALUES<\/span>(1<\/span>,'USA'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Drop tables that have foreign key constraints<\/h2>\n\n\n\n
Suppose that you want to drop the countries<\/code> and cities<\/code> tables.<\/p>\n\n\n\n
First, drop the table countries<\/code> :<\/p>\n\n\n
DROP<\/span> TABLE<\/span> countries;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued this error:<\/p>\n\n\n
Error<\/span> Code: 3730.<\/span> Cannot drop table 'countries'<\/span> referenced by a foreign key constraint 'cities_ibfk_1'<\/span> on table 'cities'<\/span>.<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
To fix this, you have two options:<\/p>\n\n\n\n
    \n
  • Drop the table cities<\/code> first and then remove the table countries<\/code>.<\/li>\n\n\n\n
  • Disable foreign key checks and drop tables in any sequence.<\/li>\n<\/ul>\n\n\n\n
    We’ll demonstrate the second way which disables the foreign key constraint check before dropping the tables.<\/p>\n\n\n\n
    Second, disable the foreign key check:<\/p>\n\n\n
    SET<\/span> foreign_key_checks = 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, drop both tables countries<\/code> and cities<\/code>:<\/p>\n\n\n
    DROP<\/span> TABLE<\/span> countries;\nDROP<\/span> TABLE<\/span> cities;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Both statements were executed successfully.<\/p>\n\n\n\n
    Finally, enable the foreign key check:<\/p>\n\n\n
    SET<\/span> foreign_key_checks = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the SET foreign_key_checks = 0<\/code>\u00a0to disable foreign key checks in MySQL.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
The following query<\/a> returns the contents of the table cities<\/code>:<\/p>\n\n\n
The table cities<\/code> has a foreign key constraint<\/a> that references the column country_id<\/code> of the table countries<\/code>.<\/p>\n\n\n\n
Second, create another table<\/a> named cities<\/code>:<\/p>\n\n\n