MySQL中对三种约束的支持

引言

文章地址：http://leekai.me/?p=198
关系型数据库通过约束机制可以保证数据的完整性。数据完整性通常由三种形式：
1. 实体完整性：即表中有一个主键。
2. 域完整性：数据值满足指定的条件。
3. 参照完整性：表示与参照表的关系和数据约束，即外键。

一、MySQL中的数据完整性

注：以下所讨论的主题均基于InnoDB存储引擎。

1.实体完整性

实体完整性在MySQL中表现为设置主键约束和唯一性约束，即primary key与unique key。这两个约束均会在数据库中创建对应的索引。

2.域完整性

域完整性又称为用户自定义完整性，保证数据值满足用户指定的条件。在InnoDB中域的完整性通过以下几点：Default（数据的默认值），not null（数据非空）。在InnoDB中不支持check约束，可以通过enum类型变量去约束离散的值，或者设置触发器来检查数据合法值。

3.参照完整性

参照完整性通过外键实现，并会创建索引。

InnoDB中的约束：

InnoDB支持以下几种约束：
- primary key
- unique key
- foreign key
- default
- not null
对应的表格如下：

约束类型	MySQL	SQL Server	Oracle
主键约束	生成唯一索引	生成唯一索引	使用已存在的索引或者创建新索引
外键约束	生成索引	不生成索引	不生成索引
唯一约束	生成唯一索引	生成唯一索引	使用已存在的索引或者创建新索引

二、MySQL中的check约束

MySQL所有的存储引擎均不支持check约束，MySQL会对check子句进行分析，但是在插入数据时会忽略，因此check并不起作用，因此实现对数据约束有两种方法：a：在mysql种约束，如使用enum类型或者触发器等。b：在应用程序里面对数据进行检查再插入。
这里着重讨论再mysql种使用enum和触发器约束数据。

1. enum类型

enum类型将数据的取值限定在在一个离散的集合中。在创建表的时候可以制定数据为enum
如：

create table t (
    id int auto_increment primary key,
    sex enum('F', 'M'),
    name varchar(20) not null
);

在MySQL中，enum的变量是按照整数与其列表对应，从1开始，比如上面的表中F的索引为1，M的索引为2。表中的输入如下：

mysql> select * from t;
+----+------+------+
| id | sex  | name |
+----+------+------+
|  1 | F    | z    |
|  2 | M    | t    |
|  3 | F    | k    |
+----+------+------+
3 rows in set (0.02 sec)

如果要检索根据sex列检索数据，则可以根据枚举中的字符串检索，结果如下

mysql> select * from t where sex = 'F';
+----+------+------+
| id | sex  | name |
+----+------+------+
|  1 | F    | z    |
|  3 | F    | k    |
+----+------+------+
2 rows in set (0.03 sec)

还可以根据枚举列的索引检索，结果如下，其中1代表F的索引为1。

mysql> select * from t where sex = 1;
+----+------+------+
| id | sex  | name |
+----+------+------+
|  1 | F    | z    |
|  3 | F    | k    |
+----+------+------+
2 rows in set (0.02 sec)

当插入值不符合枚举列表中的值时，即插入非法数据会发生什么呢，如下：

mysql> insert into t (sex, name) value ('Mae','y' );
Query OK, 1 row affected, 1 warning (0.03 sec)

执行结果有一个warning，查看warning如下：

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'sex' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.02 sec)

数据被截断，可以看出此时数据库并没有拒绝插入，而是数据被截断，具体是截断成什么，可以查看一下：

mysql> select * from t;
+----+------+------+
| id | sex  | name |
+----+------+------+
|  1 | F    | z    |
|  2 | M    | t    |
|  3 | F    | k    |
|  4 |      | y    |
+----+------+------+

结果显示是一个空字符串（但不同于null），这里优于sql_mode设置的问题（注：Mysql之SQL Mode用法详解），因此数据库会接受插入的非法值，但提示warning。
并且空字符串的索引为0，可以通过使用sex列检索数据验证：

mysql> select * from t where sex = 0;
+----+------+------+
| id | sex  | name |
+----+------+------+
|  4 |      | y    |
+----+------+------+
1 row in set (0.03 sec)

将sql_mode设置为STRICT_TRANS_TABLES会使得数据库服务器在插入非法值时拒绝：

mysql> insert into t (sex, name) value ('Mae','y' );
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

2.触发器^[1]

enum仅仅对离散的取值奏效，当遇到需要限定数据范围的时候，enum则不能使用，因此可以使用触发器来实现约束。
触发器会在insert，delete和update命令之前或者之后自动调用sql命令或存储过程。
创建触发器命令：

create [definer = { user | current_user}]
trigger trigger_name before|after  insert|update|delete
on tbl_name for each row trigger_stmt;

可以在数据更新或者插入异常数据可以根据创建的触发器对其进行操作。

附：
[1] MySQL技术内幕：InnoDB存储引擎