MySQL - SET FOREIGN_KEY_CHECKS - Check or Not Foreign Key Constraints

FOREIGN_KEY_CHECKS option specifies whether or not to check foreign key constraints for InnoDB tables.

Quick Example:

   -- Specify to check foreign key constraints (this is the default)
    SET FOREIGN_KEY_CHECKS = 1;
 
   -- Do not check foreign key constraints
    SET FOREIGN_KEY_CHECKS = 0;

Overview

MySQL FOREIGN_KEY_CHECKS option:

Syntax SET FOREIGN_KEY_CHECKS = 0 | 1
Default 1 Foreign keys are checked
When Set to 1 Existing data are not re-validated
Get the Current Value SELECT @@FOREIGN_KEY_CHECKS

Last Update: MySQL 5.6

When to Use

Temporarily disabling referential constraints (set FOREIGN_KEY_CHECKS to 0) is useful when you need to re-create the tables and load data in any parent-child order.

Without this option, it may require a lot of effort to define the correct parent-child order especially if you have a lot of tables, and a table can be a parent for some tables, and a child for others.

But as a result, you can insert data that violate foreign key constraints, and when you enable the referential constraints (set FOREIGN_KEY_CHECKS to 1), MySQL does not re-validate the inserted rows.

As an alternative, you can firstly create tables without foreign key constraints, load data and then create foreign keys using ALTER TABLE statements.

FOREIGN_KEY_CHECKS Details

In MySQL InnoDB storage engine, you can use foreign keys to set referential constraints between parent and child tables.

By default, FOREIGN_KEY_CHECKS option is set to 1, and InnoDB does not allow inserting a row that violates a foreign key constraint:

  -- Specify to check referential constraints
  SET FOREIGN_KEY_CHECKS = 1;
 
  -- Create a parent table
  CREATE TABLE states
  (
     abbr CHAR(2) PRIMARY KEY,
     name VARCHAR(90)
  ) ENGINE = InnoDB;   
 
  CREATE TABLE cities
  (
     name VARCHAR(90),
     state CHAR(2),
     FOREIGN KEY (state) REFERENCES states(abbr) 
  ) ENGINE = InnoDB;   
 
  -- Try to insert a row to child table (corresponding rows does not exist in the parent table)
  INSERT INTO cities VALUES ('Boston', 'MA');
  -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

You can disable referential integrity checks, and insert a row that violates FOREIGN KEY constraint:

    -- Do not check referential constraints
  SET FOREIGN_KEY_CHECKS = 0;
 
  -- Now we can insert row
  INSERT INTO cities VALUES ('Boston', 'MA');
  -- Query OK, 1 row affected (0.03 sec)

Then when you enable foreign key constraints check, MySQL does not re-validate data, but does not allow inserting rows that violate the foreign key constraints anymore:

  -- Specify to check referential constraints
  SET FOREIGN_KEY_CHECKS = 1;
 
  INSERT INTO cities VALUES ('New York', 'NY');
  -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
 
  SELECT * FROM states;
  -- Empty set (0.00 sec)
 
  -- Row violating foreign key constraint still exists
  SELECT * FROM cities;
  -- | Boston | MA |

The Right Way to Create FOREIGN KEY Constraint in MySQL

Many databases (Oracle, Sybase SQL Anywhere i.e) allow a simplified syntax to specify a foreign key constraint:

  CREATE TABLE cities
  (  ...
     state CHAR(2) REFERENCES states      -- state column references the primary key in states table
     ...  );

This syntax is valid in MySQL, but still does not create FOREIGN KEY:

MySQL:

  CREATE TABLE cities
  (
     name VARCHAR(90),
     state CHAR(2) REFERENCES states   
  ) ENGINE = InnoDB; 
  -- Query OK, 0 rows affected (0.05 sec)
 
  -- Let's see DDL
  SHOW CREATE TABLE cities;  
 
  -- It does not have FOREIN KEY constraint
  CREATE TABLE `cities` (
  `name` varchar(90) DEFAULT NULL,
  `state` char(2) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

You have to use FOREIGN KEY clause in CREATE TABLE to specify a foreign key in MySQL:

  DROP TABLE IF EXISTS cities;
 
  CREATE TABLE cities
  (
     name VARCHAR(90),
     state CHAR(2),
     FOREIGN KEY (state) REFERENCES states(abbr) 
  ) ENGINE = InnoDB;   
 
  -- Let's see DDL now
  SHOW CREATE TABLE cities;  
 
  -- Now the table has FOREIGN KEY
  CREATE TABLE `cities` (
    `name` varchar(90) DEFAULT NULL,
    `state` char(2) DEFAULT NULL,
    KEY `state` (`state`),
    CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`state`) REFERENCES `states` (`abbr`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

MySQL Resources