MySql数据库约束

　

　　《MySQL技术内幕：InnoDB存储引擎》本书从源代码的角度深度解析了InnoDB的体系结构、实现原理、工作机制，并给出了大量实践，本着将书读薄的思想，循序渐进的记录对本书学习的读书笔记。

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

（1）实体完整性保证表中有一个主键，在InnoDB存储引擎中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性，用户还可以编写一个触发器来保证数据完整性、

（2）域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎中，域完整性可以通过以下途径来保证：

　　a. 选择适合的数据类型确保一个数据值满足条件

　　b. 外键（Foreign Key）约束

　　c. 编写触发器

　　d. 还可以考虑用default约束作为强制域完整性的一个方面

（3）参照完整性保证两张表之间的关系，InnoDB存储引擎提供了以下几种约束：

　　a. primary key

　　b. unique key

　　c. foreign key

　　d. default

　　e. not null

1. 约束的创建和查找

　　约束的创建有以下两种方式：

　　（1）表建立时就进行约束定义

　　（2）利用alter table命令来进行创建约束

　　以下几点需要关注和注意：

　　a. 对Unique Key(唯一索引)的约束，用户除了在创建时约定，还可以通过Create Unique Index来创建

　　b. 对于主键约束耳音，其默认约束名为PRIMARY，而对于Unique Key约束而言，默认约束名和列名一样，当然也可以人为的指定Unique Key的名字，Foreign Key约束似乎会有一个比较神秘的默认名称

1.1 例1：创建表u,设置一个primary key和unique key

mysql> Create table u(
    -> id int,
    -> id_card varchar(18),
    -> name varchar(20),
    -> primary key(id),
    -> unique key(name)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.56 sec)

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.table_constraints
    -> where table_schema='test' and table_name='u';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| name            | UNIQUE          |
+-----------------+-----------------+
2 rows in set (0.01 sec)

　　可以看到，约束名如上面所说，主键的约束名为PRIMARY，唯一索引的默认约束名与列名相同。

1.2 例2：alter table创建约束

mysql> alter table u
    -> add unique key uk_id(id_card);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.table_constraints
    -> where table_schema='test' and table_name='u';
+-----------------+-----------------+
| constraint_name | constraint_type |
+-----------------+-----------------+
| PRIMARY         | PRIMARY KEY     |
| name            | UNIQUE          |
| uk_id           | UNIQUE          |
+-----------------+-----------------+
3 rows in set (0.00 sec)

1.3 例3：Foreign key的约束

mysql> create table p(
    -> id int,
    -> u_id int,
    -> primary key(id),
    -> foreign key(u_id) references p(id)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql> select constraint_name,constraint_type
    -> from
    -> information_schema.table_constraints
    -> where table_schema='test' 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库下所有的约束信息。

2. 对错误数据的约束

　　在某些默认设置下，MySql数据库允许非法或不正确的数据的插入或更新，又或者可以在数据库内部将其转化为一个合法的值，如向not null的字段插入一个null值，MySql数据库会将其更改为0再进行插入，因此数据库本身没有对数据的正确性进行约束。

2.1 例1

mysql> Create table a(
    -> id int not null,
    -> data date not null
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into a select NULL, '2009-02-20';
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from a;
+----+------------+
| id | data       |
+----+------------+
|  0 | 2009-02-20 |
+----+------------+
1 row in set (0.00 sec)

　　通过设置参数set sql_mode='strict_trans_tables';对MySql数据库的输入值进行了约束，而且针对不同的错误提示错误内容也不同。

2.1 例2：enum与set约束

mysql> Create table a(
    -> id int,
    -> sex enum('male','female')
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into a select 1,'male';
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into a select 2,'hello';
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

3. 触发器与约束

3.1 触发器的认识

　　触发器的作用是在执行insert，delete和update命令之前或之后自动调用sql命令或存储过程

3.1.1  触发器的创建

Create
[definer = { user | current_user}]
trigge trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE
on tble_name FOR EACH ROW trigger_stmt

（1）最多可以为一个表建立6个触发器，即分别为insert，update，delete的before和after各定义一个

（2）只有表才支持触发器，视图不支持（临时表也不支持）

（3）如果before触发器失败，则MySQl将不执行请求的操作，此外如果before触发器或语句本身失败，MySql将不执行after触发器（如果有的话）

3.1.2  触发器的删除

　　DROP TRIGGER trigger_name;

注：触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建

3.2 触发器约束

　　假设有张用户消费表，每次用户购买一样物品后其金额都是减的，若这时有不坏好意的用户做了一个类似减去一个负值的操作，这样用户的钱没有减少反而不断增加

mysql> Create table usercash(
    -> userid int not null,
    -> cash int unsigned not null
    -> ,
    -> Primary key(userid))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into usercash select 1,1000;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update usercash set cash=cash-(-20) where userid = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from usercash;
+--------+------+
| userid | cash |
+--------+------+
|      1 | 1020 |
+--------+------+
1 row in set (0.00 sec)

　　上面运行的SQL语句对数据库来说没有任何的问题，都可以正常运行，不会报错。但从业务逻辑上来说，这是绝对错误的。下面采用触发器来约束这个逻辑行为：

mysql> Create table usercash_error_log(
    -> userid int not null,
    -> old_cash int unsigned not null,
    -> new_cash int unsigned not null,
    -> user varchar(30),
    -> time datetime,
    -> primary key(userid)
    -> )engine=InnoDB;
Query OK, 0 rows affected (0.23 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_error_log select old.userid, old.cash,new.cash,US
),NOW();
    -> set new.cash=old.cash;
    -> end if;
    -> end//
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> delete from usercash;
Query OK, 1 row affected (0.05 sec)

mysql> insert into usercash select 1,1000;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> update usercash set cash=cash-(-20) where userid=1;
Query OK, 0 rows affected (0.11 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_error_log;
+--------+----------+----------+----------------+---------------------+
| userid | old_cash | new_cash | user           | time                |
+--------+----------+----------+----------------+---------------------+
|      1 |     1000 |     1020 | root@localhost | 2018-06-01 15:09:51 |
+--------+----------+----------+----------------+---------------------+
1 row in set (0.00 sec)

　　可以看出这次对于异常的数据更新通过触发器将其保存到了usercash_error_log。此外该触发器还记录了操作该SQL语句的用户和时间。　

4. 外键约束

外键用来保证参照完整性，MySQL数据库的MyIsAM存储引擎本身并不支持外键，对于外键的定义只是起到一个注释的作用，而InonoDB存储引擎则完整支持外键约束。

一般来说，称被引用的表为父表，引用的表称为子表，外键定义时的on delete和on update表示在对父表进行delete和updata操作时，对子表所做的操作。可定义的子表操作有：

（1）CASCADE

　　　表示当父表发生delete和update操作时，对相应的子表中的数据页进行delete和update操作

（2）SET FULL

　　表示当父表发生delete和update操作时，相应的子表中的数据被更新为NULL值，但是子表中对应的列必须允许为NULL值

（3）NO ACTION

　　表示父表发生delete或update操作时，抛出错误，不允许这类操作发生

（4）RESTRICT

　　表示父表发生delete或update操作时，抛出错误，不允许这类操作发生，如果定义外键时没有指定on delete或on update,RESTRICT就是默认的外键设置