MySQL开发手册(五) 约束

(1)约束类型


  • 约束分为表级约束和列级约束
    • 列级约束:对一个数据列的约束(列级约束可以在列定义时声明,也可以在列定义后声明)
    • 表级约束:对多个数据列的约束(标记约束只能在列定义后声明)
  • 约束类型
    • NOT NULL非空约束
    • PRIMARY KEY主键约束
    • UNIQUE KEY唯一约束
    • DEFAULT默认约束
    • FOREIGN KEY外键约束

(2)非空约束


  • NULL和NOT NULL
mysql> create table tb2(
    -> username varchar(20) not null,
    -> age tinyint unsigned null
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show columns from tb2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert tb2 values('TOm',null);
Query OK, 1 row affected (0.10 sec)

mysql> select * from tb2;
+----------+------+
| username | age  |
+----------+------+
| TOm      | NULL |
+----------+------+
1 row in set (0.00 sec)

mysql> insert tb2 values(null,20);
ERROR 1048 (23000): Column 'username' cannot be null

(3)主键约束


  • 每张表只能存在一个主键
  • 主键自动为NOT NULL
  • 主键约束保证记录的唯一性
  • AUTO_INCREMNET自动编号的必须与主键组合使用,但主键可以单独使用
mysql> create table tb4(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(30) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show columns from tb4;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(30)          | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert tb4 (username) values('jack');
Query OK, 1 row affected (0.01 sec)

mysql> insert tb4 (username) values('rose');
Query OK, 1 row affected (0.06 sec)

mysql> select * from tb4;
+----+----------+
| id | username |
+----+----------+
|  1 | jack     |
|  2 | rose     |
+----+----------+
2 rows in set (0.00 sec)
  • 主键无需与AUTO_INCREMNET,但不能重复
mysql> CREATE TABLE tb4(
    -> id SMALLINT PRIMARY KEY,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW COLUMNS FROM tb4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | smallint(6) | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT tb4 VALUES(22,'a');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT tb4 VALUES(21,'a');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT tb4 VALUES(22,'a');
ERROR 1062 (23000): Duplicate entry '22' for key 'PRIMARY'

mysql> SELECT * FROM tb4;
+----+------+
| id | name |
+----+------+
| 21 | a    |
| 22 | a    |
+----+------+
2 rows in set (0.00 sec)

(4)唯一约束


  • 唯一约束可以保证记录的唯一性
  • 唯一约束的字段可以为NULL,只能有一个NULL
  • 一张表可以存在多个唯一约束(与主键的区别)
mysql11>CREATE TABLE tb5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.06 sec)

mysql11>SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql11>INSERT tb5(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.03 sec)

mysql11>INSERT tb5(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
mysql11>select * from tb5;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | Tom      |   22 |
+----+----------+------+
1 row in set (0.00 sec)

(5)默认约束


  • 插入记录时,如果没有为字段赋值,则自动赋予默认值
mysql11>CREATE TABLE tb6(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex ENUM('1','2','3') DEFAULT '3'
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql11>SHOW COLUMNS FROM tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql11>INSERT tb6(username) VALUES('Tom');
Query OK, 1 row affected (0.03 sec)

mysql11>select * from tb6;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      | 3    |
+----+----------+------+
1 row in set (0.00 sec)

(6)外键约束


  • 外键约束的要求
    • 子表和父表必须使用相同的储存引擎,并且禁止使用临时表
    • 数据表的存储引擎只能为InnoDB
    • 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同
    • 外键列(子表)和参照列(父表)必须创建索引。如果外键列不存在索引的话,MySQl自动创建索引
mysql> create table province(
    -> id smallint unsigned primary key auto_increment,
    -> pname varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

//主键会自动创建索引
mysql> show create table province;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                           |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| province | CREATE TABLE `province` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//外键列和参照列必须类型一致
mysql> create table city(
    -> id smallint unsigned primary key auto_increment,
    -> cname varchar(10) not null,
    -> pid bigint,
    -> foreign key (pid) references province (id)
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> create table city(
    -> id smallint unsigned primary key auto_increment,
    -> cname varchar(10) not null,
    -> pid smallint,
    -> foreign key (pid) references province (id)                                   
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> create table city(
    -> id smallint unsigned primary key auto_increment,
    -> cname varchar(10) not null,
    -> pid smallint unsigned,
    -> foreign key (pid) references province (id)                                   
    -> );
Query OK, 0 rows affected (0.12 sec)

// 如果外键列不存在索引的话,MySQl自动创建索引
mysql> show create table city;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| city  | CREATE TABLE `city` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `cname` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show indexes from province;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| province |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.06 sec)

mysql> show indexes from city;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | pid      |            1 | pid         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.04 sec)
  • 外键约束的默认数据联系
    • 父表的参照列有数据后,子表的外键列才可以添加数据
    • 父表和子表有对应数据后,只有子表的数据删除之后才能删除父表的数据
mysql> insert city (cname,pid) values('hefei',1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t1`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`))
mysql> insert province (pname) values('anhui');
Query OK, 1 row affected (0.05 sec)

mysql> select * from province;
+----+-------+
| id | pname |
+----+-------+
|  1 | anhui |
+----+-------+
1 row in set (0.00 sec)

mysql> insert city (cname,pid) values('hefei',1);
Query OK, 1 row affected (0.09 sec)

mysql> select * from city;
+----+-------+------+
| id | cname | pid  |
+----+-------+------+
|  2 | hefei |    1 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> delete from province where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`t1`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`))

mysql> delete from city where id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from city;
Empty set (0.00 sec)

mysql> delete from province where id=1;
Query OK, 1 row affected (0.02 sec)
  • 外键约束的参照操作
    • CASCADE:从父表删除/更新后自动更新子表中匹配的行
    • SET NULL:从父表删除/更新后自设置子表中的外键列为NULL(使用该选项的前提是子表列没有指定NOT NULL)
    • RESTRICT:拒绝对父表的删除更新操作
    • NO ACTION:标准SQL关键字,与RESTRICT相同
  • on delete cascade
mysql> create table city1(
    -> id smallint unsigned primary key auto_increment,
    -> cname varchar(20) not null,
    -> pid smallint unsigned,
    -> foreign key (pid) references province (id) on delete cascade                 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> select * from province;
+----+---------+
| id | pname   |
+----+---------+
|  2 | beijing |
+----+---------+
1 row in set (0.00 sec)

mysql> select * from city1;
+----+-------+------+
| id | cname | pid  |
+----+-------+------+
|  3 | tom   |    2 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> delete from province where id=2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from city1;
Empty set (0.00 sec)
  • 外键约束的on delete set null
    • 从父表删除或者更新行,则设置子表中的外键列为NULL.使用该选项的前提是子表列没有指定NOT NULL
mysql> create table city2(                                                          
    -> id smallint unsigned primary key auto_increment,
    -> cname varchar(20) not null,
    -> pid smallint unsigned,
    -> foreign key (pid) references province (id) on delete set null                
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert province (pname) values('shanghai');
Query OK, 1 row affected (0.02 sec)

mysql> select * from province;
+----+----------+
| id | pname    |
+----+----------+
|  3 | shanghai |
+----+----------+
1 row in set (0.00 sec)

mysql> insert city2 (cname,pid) values('baoshan',3);
Query OK, 1 row affected (0.02 sec)

mysql> select * from city2;
+----+---------+------+
| id | cname   | pid  |
+----+---------+------+
|  1 | baoshan |    3 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> delete from province where id = 3;
Query OK, 1 row affected (0.10 sec)

mysql> select * from city2;
+----+---------+------+
| id | cname   | pid  |
+----+---------+------+
|  1 | baoshan | NULL |
+----+---------+------+
1 row in set (0.00 sec)

笔记资源下载链接 ,支持PDF、ENEX印象笔记、HTML,方便大家整理导入

© 著作权归作者所有
这个作品真棒,我要支持一下!
主要帮助小白掌握MySQL数据库的增删改查操作,最终提供一个工具类语句手册和完善的学习笔记
0条评论
top Created with Sketch.