数据库—六种约束

以下是我从相关资源网站学习的一些总结

约束保证数据的完整性和一致性
约束分为表级约束和列级约束

如果约束只针对某一个字段来使用，称为列级约束
如果约束针对两个或者两个以上的字段来使用，称为表级约束

数据库中的六种约束：主键约束（Primay Key）、唯一约束（Unique）、检查约束（Check）、默认约束（Default）、外键约束（Foreign Key），非空（NOT NULL）约束

主键约束

首先我们来创建一个表

mysql> CREATE TABLE tb1(    -> id SMALLINT AUTO_INCREMENT,    -> username VARCHAR(30) NOT NULL    -> );

这时会出现一个错误

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

现在，我们来分析以下原因
先看看MySQL文档是怎么介绍AUTO_INCREMENT的：
Using AUTO_INCREMENT

然后我们加上主键

mysql> CREATE TABLE tb1(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(30) NOT NULL    -> );Query OK, 0 rows affected

再看看表结构

mysql> SHOW COLUMNS FROM tb1;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(30) | NO   |     | NULL    |                |+----------+-------------+------+-----+---------+----------------+2 rows in set

可见主键自动为非空

现在我们插入几条数据

mysql> INSERT tb1(username) VALUES('Tom');mysql> INSERT tb1(username) VALUES('Scarlett');mysql> INSERT tb1(username) VALUES('Anna');mysql> INSERT tb1(username) VALUES('Rose');

在查看一下表

mysql> SELECT * FROM tb1;+----+----------+| id | username |+----+----------+|  1 | Tom      ||  2 | Scarlett ||  3 | Anna     ||  4 | Rose     |+----+----------+4 rows in set

可见id是从1开始自增的，可以保证记录的唯一性
那么能不能不从1开始自增呢 答案是可以的
我们设置AUTO_INCREMENT

mysql> ALTER TABLE tb1 AUTO_INCREMENT = 50;

插入两个数据以后，查看表

mysql> SELECT * FROM tb1;+----+----------+| id | username |+----+----------+|  1 | Tom      ||  2 | Scarlett ||  3 | Anna     ||  4 | Rose     || 50 | Jhon     || 51 | Dave     |+----+----------+6 rows in set

现在我们不设置AUTO_INCREMENT，创建新的表

mysql> CREATE TABLE tb2(    -> id SMALLINT PRIMARY KEY,    -> username VARCHAR(30) NOT NULL    -> );

查看数据表的基本结构

mysql> SHOW COLUMNS FROM tb2;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id       | smallint(6) | NO   | PRI | NULL    |       || username | varchar(30) | NO   |     | NULL    |       |+----------+-------------+------+-----+---------+-------+2 rows in set

插入两条数据，然后查询表

mysql> INSERT tb2 VALUES(5,'Tom');mysql> INSERT tb2 VALUES(20,'Rose');mysql> SELECT * FROM tb2;+----+----------+| id | username |+----+----------+|  5 | Tom      || 20 | Rose     |+----+----------+2 rows in set

可见，主键的字段是可以赋值的
那么，可不可以是相同的呢？

mysql> INSERT tb2 VALUES(20,'Anmmy');1062 - Duplicate entry '20' for key 'PRIMARY'

出现错误，主键不允许重复

以上说明了：
AUTO_INCREMENT 必须和主键一起使用，但是主键不一定和AUTO_INCREMENT 一起使用

唯一约束

现在创建一个既有主键约束又有唯一约束的数据表

mysql> CREATE TABLE tb3(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(30) NOT NULL UNIQUE KEY,    -> age TINYINT    -> );

mysql> SHOW COLUMNS FROM tb3;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| id       | smallint(6) | NO   | PRI | NULL    | auto_increment || username | varchar(30) | NO   | UNI | NULL    |                || age      | tinyint(4)  | YES  |     | NULL    |                |+----------+-------------+------+-----+---------+----------------+3 rows in set

接下来插入数据

mysql> INSERT tb3(username,age) VALUES('Rose',18);Query OK, 1 row affectedmysql> INSERT tb3(username,age) VALUES('Rose',22);1062 - Duplicate entry 'Rose' for key 'username'

在进行第二次插入的时候出现了错误，证明UNIQUE 保证了唯一性

主键约束和唯一约束最主要的区别是：
主键约束在一张表中只能有一个，但是唯一约束在一张表中可以有多个。

默认约束

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

创建一个新表

mysql> CREATE TABLE tb4(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(30) NOT NULL UNIQUE KEY,    -> sex ENUM('1','2','3') DEFAULT '3'    -> );Query OK, 0 rows affectedmysql> SHOW COLUMNS FROM tb4;+----------+-------------------+------+-----+---------+----------------+| Field    | Type              | Null | Key | Default | Extra          |+----------+-------------------+------+-----+---------+----------------+| id       | smallint(6)       | NO   | PRI | NULL    | auto_increment || username | varchar(30)       | NO   | UNI | NULL    |                || sex      | enum('1','2','3') | YES  |     | 3       |                |+----------+-------------------+------+-----+---------+----------------+3 rows in set

在性别中，1表示男，2表示女，3表示保密
在插入数据的时候，如果没有指明性别，则赋予默认值为3保密

mysql> INSERT tb4(username) VALUES('Rose');Query OK, 1 row affectedmysql> SELECT * FROM tb4;+----+----------+-----+| id | username | sex |+----+----------+-----+|  1 | Rose     | 3   |+----+----------+-----+1 row in set

外键约束

首先外检约束有一些要求：
1.数据表的存储引擎为InnoDB
2.外键列（子表）和参照列（父表）必须有具有相似的数据类型
是数字的话，长度和有无符号位都必须相同
是字符的话，长度可以不相同
3.外键列和参照列必须必须创建索引。如果外键列不存在索引，MySQL自动创建索引

mysql> CREATE TABLE province(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> pname VARCHAR(30) NOT NULL    -> );Query OK, 0 rows affectedmysql> SHOW CREATE TABLE province;+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table    | Create Table                                                                                                                                                  |+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+| province | CREATE TABLE `province` (  `id` smallint(6) NOT NULL AUTO_INCREMENT,  `pname` varchar(30) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set

可以看到ENGINE=InnoDB，满足了条件一

下面看条件二

mysql> CREATE TABLE users(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(10) NOT NULL,    -> pid BIGINT,    -> FOREIGN KEY(pid) REFERENCES province(id)    -> );1215 - Cannot add foreign key constraint

这个错误说明了，外键pid和父表的数据类型不一致(注意，有无符号位也要相同)
父表中的id为SMALLINT ，子表的pid为BIGINT
从而无法创建pid为外键

下面就可以成功

mysql> CREATE TABLE users(    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,    -> username VARCHAR(10) NOT NULL,    -> pid SMALLINT,    -> FOREIGN KEY(pid) REFERENCES province(id)    -> );Query OK, 0 rows affected

下面再看条件三

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 |+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| province |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set

此时province的id上已经有了索引
下面看看users的索引

mysql> SHOW INDEXES FROM users;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| users |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               || users |          1 | pid      |            1 | pid         | A         |           0 | NULL     | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set

除了我们设置为主键的id以外，系统自动的给pid创建了索引

检查约束

CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

mysql> CREATE TABLE tb5(    -> id INT NOT NULL,    -> username VARCHAR(10) NOT NULL    -> CHECK (id > 0)    -> );Query OK, 0 rows affectedmysql> INSERT tb5(id,username) VALUES(-1,'Tom');Query OK, 1 row affectedmysql> SELECT * FROM tb5;+----+----------+| id | username |+----+----------+| -1 | Tom      |+----+----------+1 row in set

check约束在MySQL中不起作用