1、数据完整性
关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束( constraint)机制,该机制提供了一条强大而简易的途径来保证数据库中数据的完整性。一般来说,数据完整性有以下三种形式:
实体完整性保证表中有一个主键。在 InnoDB存储引擎表中,用户可以通过定义Primary Key或 Unique Key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性。
域完整性保证数据每列的值满足特定的条件。在 InnoDB存储引擎表中,域完整性可以通过以下几种途径来保证:
- 选择合适的数据类型确保一个数据值满足特定条件。
- 外键( Foreign Key)约束。
- 编写触发器。
- 还可以考虑用 DEFAULT约束作为强制域完整性的一个方面。
参照完整性保证两张表之间的关系。 InnoDB存储引擎支持外键,因此允许用户定义外键以强制参照完整性,也可以通过编写触发器以强制执行。
对于 InnoDB存储引擎本身而言,提供了以下几种约束:
- Primary Key
- Unique Key
- Foreign Key
- Default
- NOT NULL
2、约束的创建和查找
约束的创建可以采用以下两种方式:
- 表建立时就进行约束定义
- 利用 ALTER TABLE命令来进行创建约束
对 Unique Key(唯一索引)的约束,用户还可以通过命令 CREATE UNIQUE INDEX来建立。对于主键约束而言,其默认约束名为PRIMARY。而对于 Unique Key约束而言,默认约束名和列名一样,当然也可以人为指定 Unique Key约束的名字。 foreign Key约束
似乎会有一个比较神秘的默认名称。下面是一个简单的创建表的语句,表上有一个主键和一个唯一键:
mysql> create table u( -> id int, -> name varchar(20), -> id_card char(18), -> primary key(id), -> unique key(name)); Query OK, 0 rows affected (0.09 sec) mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS -> where table_schema='mytest' and table_name='u'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | name | UNIQUE | +-----------------+-----------------+ 2 rows in set (0.02 sec)
可以看到,约束名就如之前所说的,主键的约束名为 PRIMARY,唯一索引的默认约束名与列名相同。当然用户还可以通过 ALTER TABLE来创建约束,并且可以定义用户所希望的约束名,如下面这个例子:
mysql> alter table u add unique keyuk_id_card (id_card); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | name | UNIQUE | | keyuk_id_card | UNIQUE | +-----------------+-----------------+ 3 rows in set (0.01 sec)
接着来看 Foreign Key的约束。为了创建 Foreign Key,用户必须创建另一张表,例如在下面的示例中创建表p
mysql> create table p( -> id int, -> u_id int, -> primary key(id), -> foreign key(u_id) references p(id)); Query OK, 0 rows affected (0.03 sec) mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='p'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | p_ibfk_1 | FOREIGN KEY | +-----------------+-----------------+ 2 rows in set (0.00 sec)
在上面的例子中,通过 information schema架构下的表 TABLE_CONSTRAINTS来查看当前 MySQL库下所有的约束信息。对于 Foreign Key的约束的命名,用户还可以通过查看表 REFERENTIAL_CONSTRAINTS,并且可以详细地了解外键的属性,如:
mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='mytest'\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mytest CONSTRAINT_NAME: p_ibfk_1 UNIQUE_CONSTRAINT_CATALOG: def UNIQUE_CONSTRAINT_SCHEMA: mytest UNIQUE_CONSTRAINT_NAME: PRIMARY MATCH_OPTION: NONE UPDATE_RULE: RESTRICT DELETE_RULE: RESTRICT TABLE_NAME: p REFERENCED_TABLE_NAME: p 1 row in set (0.00 sec)
3、约束和索引的区别
在前面已经看到 Primary Key和 Unique Key的约束,有人不禁会问:这不就是通常创建索引的方法吗?那约束和索引有什么区别呢?
的确,当用户创建了一个唯一索引就创建了一个唯一的约束。但是约束和索引的概念还是有所不同的,约束更是一个逻辑的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库中还代表着物理存储的方式。
4、对错误数据的约束
在某些默认设置下, MySQL数据库允许非法的或不正确的数据的插入或更新,又或者可以在数据库内部将其转化为一个合法的值,如向 NOT NULL的字段插人一个NULL值, My SQL数据库会将其更改为0再进行插入,因此数据库本身没有对数据的正确性进行约束。例如:
mysql> show variables like 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> create table a( -> id int not null, -> date date not null); Query OK, 0 rows affected (0.03 sec) mysql> insert into a select null,'2018-02-30'; Query OK, 1 row affected, 2 warnings (0.01 sec) Records: 1 Duplicates: 0 Warnings: 2 mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1048 Message: Column 'id' cannot be null *************************** 2. row *************************** Level: Warning Code: 1264 Message: Out of range value for column 'date' at row 1 2 rows in set (0.00 sec)
在上述例子中,首先向 NOT NULL的列插入了一个NULL值,同时向列date插入了一个非法日期'2009-02-30。“奇怪”的是 MySQL数据库并没有报错,而是显示了警告( warning)。如果用户想通过约束对于数据库非法数据的插入或更新,即 MySQL数据库提示报错而不是警告,那么用户必须设置参数 sql mode,用来严格审核输入的参数,如:
mysql> insert into a select null,'2018-02-30'; ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into a select 1,'2018-02-30'; ERROR 1292 (22007): Incorrect date value: '2018-02-30' for column 'date' at row 1
5、ENUM和SET约束
MySQL数据库不支持传统的 CHECK约束,但是通过ENUM和SET类型可以解决部分这样的约束需求。例如表上有一个性别类型,规定域的范围只能是male或 female,在这种情况下用户可以通过ENUM类型来进行约束
mysql> create table a( -> id int, -> sex enum('male','female')); Query OK, 0 rows affected (0.02 sec) mysql> insert into a select 1,'female'; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a select 2,'bi'; ERROR 1265 (01000): Data truncated for column 'sex' at row 1
这次对非法的输入值进行了约東,但是只限于对离散数值的约束,对于传统CHECK约束支持的连续值的范围约束或更复杂的约,ENUM和SET类型还是无能为力,这时用户需要通过触发器来实现对于值域的约束。
6、触发器与约束
完整性约束通常也可以使用触发器来实现,因此在了解数据完整性前先对触发器来做一个了解。
触发器的作用是在执行 INSERT、 DELETE和 UPDATE命令之前或之后自动调用SQL命令或存储过程。MSQL5.0对触发器的实现还不是非常完善,限制比较多,而从My SQL5.1开始触发器已经相对稳定,功能也较之前有了大幅的提高。
创建触发器的命令是 CREATE TRIGGER,只有具备 Super权限的 MySQL数据库用户才可以执行这条命令:
最多可以为一个表建立6个触发器,即分别为 INSERT、 UPDATE、 DELETE的BEFORE和 AFTER各定义一个。 BEFORE和 AFTER代表触发器发生的时间,表示是在每行操作的之前发生还是之后发生。当前 MySQL数据库只支持 FOR EACH ROW的触发
方式,即按每行记录进行触发,不支持像DB2的 FOR EACH STATEMENT的触发方式通过触发器,用户可以实现 MySQL数据库本身并不支持的一些特性,如对于传统CHECK约束的支持,物化视图、高级复制、审计等特性。这里先关注触发器对于约束的支持。
假设有张用户消费表,每次用户购买一样物品后其金额都是减的,若这时有“不怀好意”的用户做了类似减去一个负值的操作,这样用户的钱没减少反而会不断增加,如:
mysql> create table usercash( -> userid int not null, -> cash int unsigned not null); Query OK, 0 rows affected (0.03 sec) mysql> insert into usercash select 1,1000; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update usercash set cash=cash-(-20) where userid=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
上述运行的SQL语句对数据库来说没有任何问题,都可以正常的运行,不会报错。
但是从业务的逻辑上来说,这是绝对错误的。因为消费总是意味着减去一个正值,而不是负值,所以这时要通过触发器来约束这个逻辑行为,可以进行如下设置:
mysql> 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); Query OK, 0 rows affected (0.02 sec) mysql> delimiter ? mysql> 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; -> ? Query OK, 0 rows affected (0.05 sec) mysql> delimiter ;
上述例子首先创建了一张表 usercash_err_log来记录错误数值更新的日志,然后创建了进行约束操作的触发器 tgr usercash update,其类型为 BEFORE。触发器首先判断新、旧值之间的差值,在正常情况下消费总是减的,新值应该总是小于原来的值,因此大于原值的数据被判断为非法的输入,将cash值设定为原来的值,并将非法的数据更新插入表 usercash_err_log。再次运行上述的SQL语句:
mysql> delete from usercash; Query OK, 1 row affected (0.01 sec) mysql> insert into usercash select 1,1000; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update usercash set cash=cash-(-20) where userid=1; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from usercash; +--------+------+ | userid | cash | +--------+------+ | 1 | 1000 | +--------+------+ 1 row in set (0.00 sec) mysql> select * from usercash_err_log; +--------+----------+----------+----------------+---------------------+ | userid | old_cash | new_cash | user | time | +--------+----------+----------+----------------+---------------------+ | 1 | 1000 | 1020 | root@localhost | 2018-10-16 18:24:19 | +--------+----------+----------+----------------+---------------------+ 1 row in set (0.01 sec)
可以看到这次对于异常的数据更新通过触发器将其保存到了 usercash_err_log。此外该触发器还记录了操作该SQL语句的用户及时间。通过上述的例子可以发现,创建触发器也是实现约束的一种手段和方法。
7、外键约束
外键用来保证参照完整性, MySQL数据库的 MyISAM存储引擎本身并不支持外键,对于外键的定义只是起到一个注释的作用。而 InnodB存储引擎则完整支持外键约束。外键的定义如下:
用户可以在执行 CREATE TABLE时就添加外键,也可以在表创建后通过 ALTER TABLE命令来添加。一个简单的外键的创建示例如下:
mysql> create table parent( -> id int not null, -> primary key (id)); Query OK, 0 rows affected (0.04 sec) mysql> create table child( id int, parent_id int, foreign key (parent_id) references parent(id)); Query OK, 0 rows affected (0.03 sec)
般来说,称被引用的表为父表,引用的表称为子表。外键定义时的 ON DELETE和 ON UPDATE表示在对父表进行DELETE和UPDATE操作时,对子表所做的操作,可定义的子表操作有:
- CASCADE
- SET NULL
- NO ACTION
- RESTRICT
CASCADE表示当父表发生 DELETE或 UPDATE操作时,对相应的子表中的数据也进行 DELETE或 UPDATE操作。 SET NULL表示当父表发生 DELETE或 UPDATE操作时,相应的子表中的数据被更新为NULL值,但是子表中相对应的列必须允许为NULL值。 NO ACTION表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。 RESTRICT表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。如果定义外键时没有指定 ON DELETE或 ON UPDATE,RESTRICT就是默认的外键设置。
在其他数据库中,如 Oracle数据库,有一种称为延时检查( deferred check)的外键约束,即检查在SQL语句运行完成后再进行。而目前 MySQL数据库的外键约束都是即时检查( immediate check),因此从上面的定义可以看出,在 MySQL数据库中NO ACTION和RESTRICT的功能是相同的。
在 Oracle数据库中,对于建立外键的列,一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动地对该列加一个索引,这和 Microsoft SQL Server数据库的做法一样。因此可以很好地避免外键列上无索引而导致的死锁问题的产生。例如在上述的例子中,表 child创建时只定义了外键,并没有手动指定 parent id列为索引,但是通过命令 SHOW CREATE TABLE可以发现 InnoDB存储引擎自动为外键约束的列parent_id添加了索引：
mysql> show create table child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `parent_id` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
对于参照完整性约束,外键能起到一个非常好的作用。但是对于数据的导入操作时,外键往往导致在外键约束的检查上花费大量时间。因为 MySQL数据库的外键是即时检查的,所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查,如:
mysql> SET foreign key checks =0; mysql> LOAD DATA ... mysql> SET foreign key checks =1;
