MySQL foreign_key_checks，unique_checks

最新推荐文章于 2024-10-14 08:00:00 发布

遇星

最新推荐文章于 2024-10-14 08:00:00 发布

阅读量1.6k

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/weixin_39004901/article/details/90207022

版权

MySQL 专栏收录该内容

99 篇文章 3 订阅

订阅专栏

1.foreign_key_checks控制是否进行外键约束检查。

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> create table sam(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table tom (id int primary key,fk_id int,foreign key(fk_id) references sam(id)); 
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into sam values (1,'sam');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sam values (2,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tom values (1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sam`.`tom`, CONSTRAINT `tom_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `sam` (`id`))

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tom values (1,3);
Query OK, 1 row affected (0.00 sec)

2.unique_checks名义上控制唯一约束检查，但实际上并不起作用，MySQL始终都会进行唯一性检查。

mysql> create table sam(id int primary key,name varchar(10) unique);
Query OK, 0 rows affected (0.09 sec)

mysql> desc sam;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show variables like 'unique_checks';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| unique_checks | ON    |
+---------------+-------+
1 row in set (0.05 sec)

mysql> insert into sam values (1,'sam');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sam values (1,'sam');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> set unique_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (1,'sam');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> show variables like 'unique_checks';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| unique_checks | OFF   |
+---------------+-------+
1 row in set (0.00 sec)