数据完整性

关系型数据库系统和文件系统的一个不同点是，关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。几乎所有的关系型数据库都提供了约束（constraint）机制，约束提供了一条强大而简易的途径来保证数据库中的数据完整性，数据完整性有三种形式：

1. 实体完整性　保证表中有一个主键。在InnoDB存储引擎表中，我们可以通过定义Primary Key或者Unique Key约束来保证实体的完整性。或者我们还可以通过编写一个触发器来保证数据完整性。
2. 域完整性　保证数据的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：选择合适的数据类型可以确保一个数据值满足特定条件，外键（Foreign Key）约束，编写触发器，还可以考虑用DEFAULT约束作为强制域完整性的一个方面。
3. 参照完整性　保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。对于InnoDB存储引擎而言，提供了4中约束：Primary Key，Unique Key，Foreign Key，Default，NOT NULL

约束的创建和查找

对于约束的建立，可以在表建立时就进行定义，也可以在之后使用ALTER TABLE命令来进行创建。对于Unique Key的约束，我们还可以通过Create Unique Index来进行建立。对于主键约束而言，其默认约束名为PRIMARY KEY。而对于Unique Key约束而言，默认约束名和列名一样，当然可以人为的指定一个名字。对于Foreign Key约束，似乎会有一个比较神秘的默认名称。下面是一个简单的创建表的语句，表上有一个主键和一个唯一键：

create table u (id int,name varchar(20),id_card char(18),primary key(id),unique key(name));

select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema=‘mytest‘ and table_name=‘u‘\G;

***************************1.row***************************

constraint_name：PRIMARY

constraint_type：PRIMARY KEY

***************************2.row***************************

constraint_name：name

constraint_type：UNIQUE

当然我们还可以通过ALTER TABLE来进行创建，并且可以定义约束的名字，如：

alter table u add unique key uk_id_card(id_card),

select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema=‘mytest‘ and table_name=‘u‘\G;

接着来看Foreign Key的约束，因此我们必须来创建另一张表：

create table p (id int,u_id int,primary key(id),foreign key(u_id) references p(id));

select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema=‘mytest‘ and table_name=‘u‘\G;

***************************1.row***************************

constraint_name：PRIMARY

constraint_type：PRIMARY KEY

***************************2.row***************************

constraint_name：p_ibfk_1

constraint_type：FOREIGN KEY

这里我们通过information_schema架构下的表TABLE_CONSTRAINTS来查看当前MySQL库下所有的约束。对于Foreign Key的约束的定义，我们还可以通过查看表REFERENTIAL_CONSTRAINTS，并且可以详细地了解外键的属性，如： 

select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema=‘mytest‘\G;

***************************1.row***************************

CONSTRAINT_CATALOG：NULL

CONSTRAINT_SCHEMA：test2

CONSTRAINT_NAME：p_ibfk_1

UNIQUE_CONSTRAINT_CATALOG：NULL

UNIQUE_CONSTRAINT_SCHEMA：test2

UNIQUE_CONSTRAINT_NAME：PRIMARY

MATCH_OPTION：NONE

UPDATE_RULE：RESTRICT

DELETE_RULE：RESTRICT

TABLE_NAME：p

REFERENCED_TABLE_NAME：p

约束和索引的区别

我们已经看到Primary key和Unique Key的约束。有人不禁会问，这不就是我们创建索引的方法吗？那约束和索引有什么区别呢？的确，当你创建了一个唯一索引，就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储的方式。

对于错误数据的约束

默认情况下，MySQL数据库允许非法或者不正确数据的插入或更新，或者内部将其转化为一个合法的值，如对于NOT NULL的字段插入一个NULL值，会将其更改为0再进行插入，因此本身没有对数据的正确性进行约束。

我们来看一个例子：

create table a (id int not null,date date not null);

insert into a select NULL,‘2009-02-30‘;

show warnings;

***************************1.row***************************

Level：Warning

Code：1048

Message：Column‘id‘cannot be null

***************************2.row***************************

Level：Warning

Code：1265

Message：Data truncated for column‘date‘ at row 1

select * from a;

+----+-------------+

|id|date

|0|0000-00-00

+----+-------------+

对于NOT NULL的列我插入了一个NULL值，同时插入了一个非法日期‘2009-02-30‘，MySQL都没有报错，而是显示了警告（warning）。如果我们想约束对于非法数据的插入或更新，MySQL是提示报错而不是警告，那么我们应该设置参数sql_mode，用来严格审核输入的参数，如：

set sql_mode=‘strict_trans_tables‘; 

insert into a select NULL,‘2009-02-30‘;

ERROR 1048（23000）：Column‘id‘cannot be null

insert into a select 1,‘2009-02-30‘;

ERROR 1292（22007）：Incorrect date value：‘2009-02-30‘for column‘date‘at row 1

我们的目的达到了，这次MySQL约束了输入值的合法性了，而且针对不同的错误，提示的错误内容也都不同。参数sql_mode可设的值有很多，具体的请参考MySQL官方文档。

ENUM和SET约束

MySQL不支持传统的CHECK约束，但是通过ENUM和SET类型可以解决部分这样的约束需求。如我们的表上有一个性别类型，规定域的范围只能是male或者female，这种情况下我们可以通过ENUM类型来进行约束：

create table a (id int,sex enum(‘male‘,‘female‘));

insert into a select 1,‘female‘;

insert into a select 2,‘bi‘;

Records：1 Duplicates：0 Warnings：1

可以看到，对于第二条记录的插入依然是抱了警告。因此如果想实现CHECK约束，还需要设置参数sql_mode：

SET sql_mode=‘STRICT_TRANS_TABLES‘;

insert into a select 2,‘bi‘;

ERROR 1265（01000）：Data truncated for column‘sex‘at row 1

这次对于非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统CHECK约束支持的连续值的范围约束或者更复杂的约束，ENUM和SET类型还是无能为力，这时我们就需要通过触发器来实现约束了。

触发器与约束

完整性约束通常也可以使用触发器来实现，触发器的作用是在INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或者存储过程。MySQL 5.0对于触发器的实现还不是非常完善，限制比较多；而从MySQL 5.1开始，触发器已经相对稳定，功能也较之前有了大幅的提高。

创建触发器的命令是CREATE TRIGGER，只有具备Super权限的MySQL用户才可以执行这条命令：

CREATE  

[DEFINER={user|CURRENT_USER}]

TRIGGER trigger_name

BEFORE|AFTER   INSERT|UPDATE|DELETE

ON tbl_name

FOR EACH ROW trigger_stmt

最多可以为一个表建立5个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。BEFORE和AFTER代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生。当前MySQL只支持FOR EACH ROW的触发方式，即按每行记录进行触发，不支持如DB2的FOR EACH STATEMENT的触发方式。

通过触发器，我们可以实现MySQL数据库本身并不支持的一些特性，如对于传统CHECK约束的支持、物化视图、高级复制、审计等特性。这里我们先关注触发器对于约束的支持。

我们考虑用户消费表，每次用户购买一样物品后其金额都是减的，若这时有不怀好意的人做了类似减去一个负值的操作，这样的话用户的钱没减少反而会不断地增加。

create table usercash(userid int,cash int unsigned not null);

insert into usercash select 1,1000;

update usercash set cash=cash-(-20) where userid=1;

对于数据库来说，上述的内容没有任何问题，都可以正常运行，不会报错。但是从业务的逻辑上来说，这是错误的，消费总是应该减去一个正值，而不是负值。因此这时如果通过触发器来约束这个逻辑行为的话，可以如下操作：

外键

InnoDB的约束机制