Cannot add or update a child row: a foreign key constraint fails ( `classicmodels`. MySQL issued the following error: Error Code: 1452. VALUES( 'New York', 1) Code language: SQL (Structured Query Language) ( sql ) Third, insert a new row into the cities table: INSERT INTO cities(city_name, country_id) The table cities has a foreign key constraint that refers to the column country_id of the table countries. ) ENGINE= InnoDB Code language: SQL (Structured Query Language) ( sql ) Second, create another table named cities: CREATE TABLE cities( Disable foreign key check exampleįirst, create a new table named countries: CREATE TABLE countries(Ĭode language: SQL (Structured Query Language) ( sql ) In other words, MySQL will not verify the consistency of the data that was added during the foreign key check disabled. Notice that setting foreign_key_checks to 1 does not trigger any validation of the existing table data. To re-enable foreign key constraint check, you set the value of the foreign_key_checks to 1: SET foreign_key_checks = 1 Code language: SQL (Structured Query Language) ( sql ) To disable foreign key checks, you set the foreign_key_checks variable to zero as follows: SET foreign_key_checks = 0 Code language: SQL (Structured Query Language) ( sql ) Unless you disable the foreign key checks, you cannot drop a table referenced by a foreign key constraint. 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.Īnother scenario that you want to disable the foreign key check is when you want to drop a table. For example, you can load data to the parent and child tables in any order with the foreign key constraint check disabled. Sometimes, it is very useful to disable foreign key checks. Summary: in this tutorial, you will learn how to disable foreign key constraint checks in MySQL.
0 Comments
Leave a Reply. |