MySQL中的约束

最新推荐文章于 2025-04-04 17:47:22 发布

原创最新推荐文章于 2025-04-04 17:47:22 发布 · 298 阅读

0 ·

CC 4.0 BY-SA版权

精选博客同时被 2 个专栏收录

85 篇文章

订阅专栏

MySQL学习目录

9 篇文章

订阅专栏

1、MySQL外键约束的要求解析

约束保证数据的完整性和一致性
约束分为表级约束和列级约束：对一个数据列建立的约束，称为列级约束；对多个数据列建立的约束，称为表级约束。列级约束既可以在列定义时声明，也可以在列定义后声明。表级约束只能列定义后声明。
约束类型包括（按功能分类）：NOT NULL，PRIMARY KEY，UNIQUE KEY，DEFAULT，FOREIGN KEY
外键约束（实现一对一或一对多关系）要求：

父表与子表必须具有相同的存储引擎，而且禁用使用临时表
数据表的存储引擎必须为InnoDB
外键列与参照列必须具有相似的数据类型，其中数字的长度或有无符号位必须相同，而字符的长度可以不同。

mysql> use test;

Database changed

父表：

mysql> CREATE TABLE provinces(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> pname VARCHAR(20) NULL);

Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE provinces;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

错误的子表演示：（参照列）
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(10) NOT NULL,
-> pid BIGINT,
-> FOREIGN KEY(pid) REFERENCES provinces(id)
-> );
ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

子表：（外键列）
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY(pid) REFERENCES provinces(id)
-> );
Query OK, 0 rows affected (0.02 sec)

显示索引：

参照列以网格的形式展示：

mysql> SHOW INDEXES FROM provinces\G;
*************************** 1. row ***************************
Table: provinces
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

外键列索引：（外键列不存在索引的话，会自动创建）

mysql> SHOW INDEXES FROM users\G;
*************************** 1. row ***************************
Table: users
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

显示users的数据表：

2、外键约束的参照操作

（1）CASCADE 从父表删除或更新且自动删除或更新子表中的匹配

mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT provinces(pname) VALUES('A');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.00 sec)

mysql> INSERT users1(username,pid) VALUES('Tom',3);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT users1(username,pid) VALUES('John',2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | Tom      |    3 |
|  2 | John     |    2 |
+----+----------+------+
2 rows in set (0.00 sec)

删除操作的关联：

mysql> DELETE FROM provinces WHERE id=3;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  2 | John     |    2 |
+----+----------+------+
1 row in set (0.00 sec)

（2）SET NULL 从父表删除或更新行，并设置子表中的外键列为NULL 。如果使用该选项，必须保证子表列没有指定NOT NULL
（3）RESTRICT:拒绝对父表的删除或更新操作

（4）NO ACTION: 标准SQL的关键字，在MySQL中与RESTRICT相同。

总结:

在外键约束当中，在实际的开发过程中，我们很少使用物理的外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有INNODB这种引擎才会支持，像我们另外的一种引擎MYISAM的引擎则不支持，反过来说，如果我想创建的数据表，假设存储引擎为MYISAM，而且又想使用外键约束的话，其实是不可能实现的，所以说，我们在实际的项目开发中，我们不去定义物理的外键，所谓的逻辑外键指的是就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义。

补充：

1.表级约束与列级约束
（1）对一个数据列建立的约束，称为列级约束
（2）对多个数据列建立的约束，称为表级约束
（3）列级约束既可以在列定义时声明，也可以在列定以后声明
（4）表级约束只能在列定义后声明
2.主键、外键、非空、唯一、default都是约束条件。主键、外键、唯一既可以作为表级约束，也可作为列级约束
（ not null， default ）只有列级约束