SET FOREIGN_KEY_CHECKS命令

ll

• ​​写在前面​​
• ​​正文​​

• ​​Overview​​
• ​​When to Use​​
• ​​FOREIGN_KEY_CHECKS Details​​

写在前面

刚在import xx.sql文件的时候，使用的是 Navicat Data Model

现在接触的项目是基本不用外键的（foreign key）的，可能单表设计，操作SQL更方便，尤其是JPA的存在，几乎都不用写sql的，但在表的设计使用若关联，即不使用外键约束表，只是在表里加上关联字段，这样当需要联表的时候，可拼接SQL去关联查询。我这里有些例子，关于没有外键约束下的连表实现，只总结了3中方式

• JPQL方式（基于Model的操作）
• 原生SQL的方式
• QueryDsl
• 
  

后来我试着去用@OneToMany,@MnayToMany等等，做表的外键约束，配置级联关系（可以选择在CRUD操作时的级联操作），JPA下查询是挺方便的，多表查也很方便，是自动关联的，但CUD操作时，就很头疼了，你要关心更多的Many 或者One的映射关系，否则会出现，外键的各种异常，后来在设计表结构时，就很少再做外键映射了…

正文

SET FOREIGN_KEY_CHECKS，是外键检查的，如果设置为零的话，是不是在有外键约束的时候，就不会出现外键的各种异常了？？还没试。。。。
怕翻译不准，就没去翻译，自己留着学习用，

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