InnoDB的约束机制

时间:2021-11-27 21:32:10

数据完整性

关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。几乎所有的关系型数据库都提供了约束(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官方文档。

InnoDB的约束机制

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;

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

|userid|old_cash|new_cash|user|time

|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操作时子表所做的操作。可定义的子表操作有:

  1. CASCADE:当父表发生DELETE或UPDATE操作时,相应的子表中的数据也被DELETE或UPDATE。
  2. SET NULL:当父表发生DELETE或UPDATE操作时,相应的子表中的数据被更新为NULL值。当然,子表中相对应的列必须允许NULL值。
  3. NO ACTION:当父表发生DELETE或UPDATE操作时,抛出错误,不允许这类操作发生。
  4. 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;