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;

MySQL FOREIGN_KEY_CHECKS option:

Last Update: MySQL 5.6

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.

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 |

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

• Reference and Migration

MySQL 5.6 Documentation

• FOREIGN_KEY_CHECKS option
• InnoDB FOREIGN KEY Constraints