SET FOREIGN_KEY_CHECKS命令

古城微笑少年丶 2024-04-17 05:30 14阅读 0赞

### ll ###

*   *  写在前面
     *  正文
     *   *  Overview
         *  When to Use
         *  FOREIGN\_KEY\_CHECKS Details

## 写在前面 ##

刚在import xx.sql文件的时候，使用的是 Navicat Data Model  
![在这里插入图片描述][20190822144734576.png]  
现在接触的项目是基本不用外键的（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

[20190822144734576.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/e882936356b84866a00336e8d95d10cc.png