InnoDB的约束机制

数据完整性

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

实体完整性　保证表中有一个主键。在InnoDB存储引擎表中，我们可以通过定义Primary Key或者Unique Key约束来保证实体的完整性。或者我们还可以通过编写一个触发器来保证数据完整性。
域完整性　保证数据的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下几种途径来保证：选择合适的数据类型可以确保一个数据值满足特定条件，外键（Foreign Key）约束，编写触发器，还可以考虑用DEFAULT约束作为强制域完整性的一个方面。
参照完整性　保证两张表之间的关系。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;

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

create table usercash_err_log(

　　userid int not null,

　　old_cash int unsigned not null,

　　new_cash int unsigned not null,

　　user varchar(30),

　　time datetime);

delimiter$$

create trigger tgr_usercash_update before update on usercash

　　for each row

　　begin

　　　　if new.cash-old.cash＞0 then

　　　　　　insert into usercash_err_log select old.userid，old.cash，new.cash，user（），now（）；

　　　　　　set new.cash=old.cash；

　　　　end if；

　　end;

delete from usercash;

insert into usercash select 1,1000;

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

select * from usercash;

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

|userid|cash

|1|1000

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

select * from usercash_err_log;

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

|1|1000|1020|root@localhost|2009-11-06 11：49：49

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

我们创建了一张表用来记录错误数值更新的日志，首先判断新旧值之间的差值，正常情况下消费总是减的，因此新值应该总是小于原来的值，因此对于大于原值的数据，我们判断为非法的输入，将cash值设定为原来的值。

外键

外键用来保证参照完整性，MySQL默认的MyISAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用。InnoDB存储引擎则完整支持外键约束。外键的定义如下：

[CONSTRAINT[symbol]] FOREIGN KEY

[index_name](index_col_name,……)

REFERENCES tbl_name (index_col_name,……)

[ON DELETE reference_option]

[ON UPDATE reference_option]

reference_option:

RESTRICT|CASCADE|SET NULL|NO ACTION

我们可以在CREATE TABLE时就添加外键，也可以在表创建后通过ALTER TABLE命令来添加。

一个简单的外键的创建示例如下：

CREATE TABLE parent(

　　id INT NOT NULL,

　　PRIMARY KEY(id)

)ENGINE=INNODB；

CREATE TABLE child(

　　id INT,

　　parent_id INT,

　　index par_ind(parent_id),

　　FOREIGN KEY(parent_id) REFERENCES parent(id)

)ENGINE=INNODB；

一般来说，我们称被引用的表为父表，另一个引用的表为子表。外键定义为，ON DELETE和ON UPDATE表示父表做DELETE和UPDATE操作时子表所做的操作。可定义的子表操作有：

CASCADE：当父表发生DELETE或UPDATE操作时，相应的子表中的数据也被DELETE或UPDATE。
SET NULL：当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值。当然，子表中相对应的列必须允许NULL值。
NO ACTION：当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。
RESTRICT：当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键时没有指定ON DELETE或ON UPDATE，这就是默认的外键设置。在Oracle中，有一种称为延时检查（deferred check）的外键约束，而目前MySQL的约束都是即时检查（immediate check）的，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的。

在Oracle数据库中，外键通常被人忽视的地方是，对于建立外键的列，一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动地对该列加一个索引，这和Microsoft SQL Server数据库的做法一样。因此可以很好地避免外键列上无索引而导致的死锁问题的产生。

对于参照完整性约束，外键能起到一个非常好的作用。但是对于数据的导入操作，外键往往导致大量时间花费在外键约束的检查上，因为MySQL的外键是即时检查的，因此导入的每一行都会进行外键检查。但是我们可以在导入过程中忽视外键的检查，如：

SET foreign_key_checks=0;

LOAD DATA……

SET foreign_key_checks=1;

posted @ 2017-01-16 19:00 wade&luffy 阅读(778) 评论(0) 编辑收藏举报

刷新页面返回顶部

wade&luffy

生活最主要的还是感受，坚持是一种刻意的练习，不断寻找缺点突破缺点的过程，而不是重复做某件事情。

InnoDB的约束机制

数据完整性

约束的创建和查找

约束和索引的区别

对于错误数据的约束

ENUM和SET约束

触发器与约束

外键

公告