MySQL(InnoDB剖析):19---table之（约束与数据完整性(唯一/主键/外键/enum/set/触发器约束、约束和索引区别、sql_mode)）

• 约束与完整性可参阅另一篇文章：https://blog.csdn.net/qq_41453285/article/details/87971552

一、数据完整性

• 关系型数据库系统和文件的一个不同点：关系数据库本身能保证存储数据的完整性，不需要应用程序的控制，而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束（constraint）机制，该机制提供了一条强大而简易的途径来保证数据库中数据的完整性
• 一般来说，数据完整性有以下三种形式：
  • 实体完整性：保证表中有一个主键。在InnoDB存储引擎表中，用户可以通过定义primary key或unique key约束来保证实体的完整性。用户还可以通过编写一个触发器来保证数据完整性
  • 域完整性：保证数据每列的值满足特定的条件。在InnoDB存储引擎表中，域完整性可以通过以下途径来保证：
    • 选择合适的数据类型确保一个数据值满足特定的条件
    • 外键约束
    • 编写触发器
    • 还可以考虑用default约束作为强制域完整性的一个方面
  • 参数完整性：保证两个表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行
• 对于InnoDB存储引擎本身而言，提供了以下几种约束：
  • primary key
  • unique key
  • foreign key
  • deafult
  • not null
• 约束的创建可以采用以下两种方式：
  • 表创建时就进行约束定义
  • 利用alter table命令来进行创建约束

二、唯一索引约束、主键约束、外键约束

• 约束名：
  • 对于唯一约束来说，默认的约束名与列名一样
  • 对于主键约束来说，其默认约束名为primary
  • 对于foreign key来说，其也有一个默认的约束名（但没有什么规则）

主键与唯一键的演示案例

• 下面创建一个表，在创建表的同时创建主键和唯一键

-- 此表在demo数据库中创建
create table u(
    id int,
    name varchar(20),
    id_card char(18),
    primary key(id),
    unique key(name)
);

 

• 查看主键的约束名和唯一键的约束名（通过数据库和表名进行查看）
  • 可以看到主键约束的默认约束名为primary
  • 唯一键的默认约束名为字段名

select constraint_name,constraint_type
from information_schema.table_constraints
where table_schema='demo' and table_name='u';

外键约束的演示案例

• 先创建一个表p

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

• 查看外键约束的名称，默认值为“p_ibfk_1”

select constraint_name,constraint_type
from information_schema.table_constraints
where table_schema='demo' and table_name='p';

• 上面都是通过information_schema数据库中的table_constraints表来查询所有的约束信息。对于外键的约束的命名，还可以通过表referential_constraints来查看比较详细的外键属性，如：

select * from information_schema.referential_constraints
where constraint_schema='demo'\G;

三、约束和索引的区别

• 我们可以使用create unique index等创建唯一索引的方式来创建一个唯一约束。但是两者有什么区别呢？当用户创建了一个唯一索引就创建了一个唯一的约束
• 但是约束和索引的概念还是不同的：
  • 约束是一个逻辑的概念，用来保证数据的完整性
  • 索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式

四、对错误数据的约束（sql_mode参数）

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

错误演示案例

• 创建一个表，字段为都为非空约束

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

• 插入数据，并且是字段1为null，并且向date列中插入了一个非法日期（2月份只有29天）。但是数据库没有报错，而是显示了警告

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

• 查看警告

show warnings\G;

• 查看表格中的数据 

select * from a;

sql_mode参数

• 上面插入了错误的数据，系统仍然允许插入，那么可以设置参数sql_mode，用来严格审核输入的参数
• sql_mode可设置的值很多，具体参考MySQL官方手册
• 例如，我们设置sql_mode为“STRICT_TRANS_TABLES”，那么MySQL数据库对于输入值的合法性进行了约束

set sql_mode='STRICT_TRANS_TABLES';

• 然后再插入错误的sql语句，那么MySQL会不允许插入：

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

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

五、enum和set约束

• MySQL数据库不支持传统的check约束，但是通过enum和set类型可以解决部分这样的约束需求

check约束

• 在数据库中，CHECK 约束是指约束表中某一个或者某些列中可接受的数据值或者数据格式。例如，可以要求 authors 表的 postcode 列只允许输入六位数字的邮政编码

• CHECK 约束可以应用于一个或者多个列，也可以将多个CHECK 约束应用于一个列

• 当除去某个表时，对这个表的CHECK 约束也将同时被去除

演示案例

• 例如表上有一个性别类型，规定域的范围只能是male或female，在这种情况下用户可以通过enum类型来进行约束

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

• 然后分别插入一行正确和错误的性别，错误的那一行仍然可以插入成功

insert into a select 1,'female';

insert into a select 2,'bi';

 

• 我们需要设置sql_mode为“STRICT_TRANS_TABLES”

set sql_mode='STRICT_TRANS_TABLES';

insert into a select 3,'bi';

• 缺点：虽然enum这次是对非法的输入值进行了约束，但是只限于对离散数值的约束，对于传统check约束支持的连续值的范围约束或更复杂的约束，enum和set类型还是无能为力，这时用户需要通过触发器来实现值域的约束

六、触发器与约束

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

演示案例

• 假设有张用户消费表，每次用户购买一样物品后其金额都是减的

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

• 若这时有“不怀好意”的用户做了类似减去一个负值的操作，这样用户的钱没减少反而会不断增加，如： 

insert into usercash select 1,1000;

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

• 上述运行的SQL语句对于数据库来说是没有任何问题的，都可以正常的运行，不会报错。但是从业务逻辑上说，绝对是错的
• 此时我们就要通过触发器来约束这个逻辑行为，可进行如下的设置：

-- 创建一张表来记录错误操作的日志
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 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;
$$

delimiter ;

• 创建了触发器之后，现在我们删除表中的数据，然后再次进行插入，减去负值的操作不允许

delete from usercash;

insert into usercash select 1,1000;

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

• 可以看到没有操作成功，并且在错误表中记录了错误的操作信息

七、外键约束

• 外键用来保证参照完整性，MySQL数据库的MyISAM存储引擎本身并不支持外键，对于外键的定义只是一个注释的作用。而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，restrict就是默认的外键设置

即时检查

• 在其他数据库中，如Oracle数据库，有一种称为“延迟检查”的外键约束，即检查在SQL语句运行完成后再进行
• 而目前MySQL数据库的外键约束都是“即时检查”，因此从上面的定义可以看出，在MySQL数据库中NO ACTION和RESTRICT的功能是相同的

外键创建演示案例

• 创建父表

create table parent(
    id int not null,
    primary key(id)
)engine=Innodb;

• 创建子表，其中的parent_id定义一个外键指向于parent表的id字段 

create table child(
    id int,
    parent_id int,
    foreign key(parent_id) references parent(id)
)ENGINE=InnoDB;

创建外键时的默认索引

• 在Oracle数据库中，对于建立外键的列，一定不要忘记给这个列加上一个索引。而InnoDB存储引擎在外键建立时会自动地对该列加一个索引，这和Microsoft SQL Server数据库的做法一样
• 因此可以很好地避免外键列上无索引而导致的死锁问题的产生
• 例如上面的例子中，表child创建时只定义了外键，并没有手动指定parent_id列为索引，但是通过show create table可以看到InnoDB自动为外键约束的列parent_id添加了索引

show create table child\G;

foreign_key_checks参数

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

-- 关闭外键检查
set foreign_key_checks=0;

-- do something
load data...

-- 开启外键检查
set foreign_key_checks=1;