【一步一步学习mysql】约束
- 约束保证数据的完整性和一致性
- 约束分为表级约束和列级约束
- 约束类型包括：
- NOT NULL（非空约束）
- PRIMARY KEY（主键约束）
- UNIQUE KEY（唯一约束）
- DEFAULT（默认约束）
- FOREIGN KEY（外键约束）
非空约束
NULL，可以为空
NOT NULL，不可以为空
mysql> CREATE TABLE user2( name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.28 sec)
mysql> SHOW COLUMNS FROM user2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
主键约束
PRIMARY KEY
- 主键约束
- 每张数据表只能存在一个主键
- 主键保证记录的唯一性
- 主键自动为NOT NULL
mysql> CREATE TABLE user3( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.23 sec)
mysql> SHOW COLUMNS FROM user3;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT user3(name, age) VALUES('xxx', 30);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT user3(name, age) VALUES('yyy', 33);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM user3;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | xxx | 30 |
| 2 | yyy | 33 |
+----+------+------+
2 rows in set (0.01 sec)
唯一约束
UNIQUE KEY
- 唯一约束
- 唯一约束可以保证记录的唯一性
- 唯一约束的字段可以为空值(NULL)
- 每张数据表可以存在多个唯一约束
mysql> CREATE TABLE user4( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE KEY, age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.30 sec)
mysql> SHOW COLUMNS FROM user4;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
默认约束
DEFAULT
- 默认值
- 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
mysql> CREATE TABLE user5( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE KEY, sex ENUM('F', 'M', 'NA') DEFAULT 'M');
Query OK, 0 rows affected (0.31 sec)
mysql> SHOW COLUMNS FROM user5;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| sex | enum('F','M','NA') | YES | | M | |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
外键约束
保持数据一致性，完整性。实现一对一或一对多关系。我觉得这就是为啥叫做关系型数据库的原因。
- 父表和子表必须使用相同的存储引擎（InnoDB），而且禁止使用临时表。
- 外键列和参照列必须具有相似的数据类型。要我说就用相同的即可。
- 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL会自动创建索引。
FOREIGN KEY(外键列) REFERENCES 父表(参照列)
# 父表
mysql> CREATE TABLE province( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.27 sec)
# 子表
mysql> CREATE TABLE users(id SMALLINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES province(id));
Query OK, 0 rows affected (0.24 sec)
# 查看子表的索引，可以看到外键列被自动加上了索引
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)
外键约束的参照操作
- CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
- SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL，前提是该外键列没有设置NOT NULL。
- RESTRICT：拒绝对父表的删除或更新操作。
# 创建一个使用参照的表 users2 【ON DELETE SET NULL ON UPDATE CASCADE】
mysql> CREATE TABLE users2(id SMALLINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES province(id) ON DELETE SET NULL ON UPDATE CASCADE);
Query OK, 0 rows affected (0.30 sec)
# 向父表和子表中进行数据填充
mysql> insert province(name) values('山东省');
mysql> insert province(name) values('江苏省');
mysql> insert province(name) values('浙江省');
mysql> insert users2(name, pid) values('a', 1);
mysql> insert users2(name, pid) values('b', 2);
mysql> insert users2(name, pid) values('c', 1);
# 更改父表中的数据，子表中的数据做出相应的改变
mysql> update province set id = 5 where id = 1;
mysql> select * from users2;
+----+------+------+
| id | name | pid |
+----+------+------+
| 1 | a | 5 |
| 2 | b | 2 |
| 3 | c | 5 |
+----+------+------+
3 rows in set (0.00 sec)
# 删除父表中的数据，子表中的数据置NULL
mysql> delete province from id = 2;
mysql> select * from users2;
+----+------+------+
| id | name | pid |
+----+------+------+
| 1 | a | 5 |
| 2 | b | NULL |
| 3 | c | 5 |
+----+------+------+
3 rows in set (0.00 sec)
注意：由于外键操作仅支持InnoDB，所以应用之前要考虑清楚。
列级约束和表级约束
- 对一个数据列建立的约束，称为列级约束。
- 对多个数据列建立的约束，称为表级约束。
- 列级约束既可以在列定义时声明，也可以在列定义后声明。
- 表级约束只能在列定义时约束。
一般都是在应用列级约束即可，不用太关注。
