MySQL约束，触发器

关系型数据库不同于文件系统，关系型数据库都会提供约束机制来保证数据的完整性

数据完整性包含3个方面

1、实体完整性，保证表中有一个主键。通过定义primary key 和unique key 保证实体完整性

2、域完整性，保证数据的值满足特定的条件。可通过外键，触发器，default，not null/null 保证域完整性

3、参照完整性，保证两张表之间的关系。Innodb支持外键，也可以通过触发器保证

Innodb提供5种约束

primary key, unique key, foreign key, default, not null

创建约束的两种方式：创建表时创建；alter table 命令创建

1、创建primary key和unique key 约束
    primary key 约束的默认名字是 primary key ，unique key 约束的默认名字是列名
    create table u(
        id int(11), 
        name varchar(20), 
        id_card char(18), 
        primary key(id), 
        unique key(name)
    ) engine=innodb;

    查看约束
    select constraint_name, constraint_type 
    from information_schema.TABLE_CONSTRAINTS 
    where table_schema=database() and table_name='u';

2、alter table 创建约束
    alter table u add unique key uk_id_card(id_card);

3、创建外键约束foreign key
    create table p( 
        id int(11), 
        u_id int(11), 
        primary key(id), 
        foreign key (u_id) references u (id) 
    );

    查看所有约束（通过information_schema.TABLE_CONSTRAINTS）
    select constraint_name, constraint_type
    from information_schema.TABLE_CONSTRAINTS 
    where table_schema=database() and table_name='p';

    查看外键约束（通过information_schema.REFERENTIAL_CONSTRAINTS）
    select * from information_schema.REFERENTIAL_CONSTRAINTS
    where constraint_schema=database();

约束和索引的区别：约束是一个逻辑的概念，用来保证数据的完整性；索引是一个数据结构，有逻辑的概念，在数据库中更是一个物理存储的方式

对于错误数据的约束：默认MySQL允许非法的或不正确的值插入或更新，或者将其转为合法的值。比如对not null 的字段插入null值，会将其转换为0，再进行插入。可以设置 sql_mode 的值来严格审核输入的值，

set sql_mode='STRICT_TRANS_TABLES';

ENUM和SET约束

    MySQL不支持传统的check约束，但是通过ENUM和SET列可以简单实现这样的约束需求。

    如性别字段只能是male和female，可以创建ENUM类型

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

    若想实现连续值的范围约束，则需要通过触发器实现。

触发器与约束

    触发器的作用是在INSERT、UPDATE、DELETE语句之前或之后自动调用sql命令或存储过程。MySQL5.1开始，触发器功能已经非常稳定。一个表最多可以创建6个触发器，即分别为INSERT、UPDATE、DELETE语句的BEFORE和AFTER各定义一个，MySQL只支持FOR EACH ROW 的触发方式。触发器的例子暂时不写了。

外键

    外键保证数据的参照完整性，MyISAM并不支持外键，定义外键只起到一个注释的作用，Innodb完全支持外键。

    创建语句：  

 [CONSTRAINT [symbol]]  FOREIGN KEY [index_name] (index_col_name, ...)
 REFERENCES table_name (index_col_name, ...)
 [ON DELETE|UPDATE  RESTRICT|CASCADE|SET NULL|NO ACTION]

 默认是   RESTRICT

 例如：
    create table parent(
        id int(11), 
        primary key (id)
    )engine=innodb;

    create table child(
        id int(11),  
        parent_id int(11), 
        primary key (id), 
        foreign key (parent_id) references parent (id)
    )engine=innodb;

       一般来说，被引用的表为父表，引用表为子表。外键定义为，ON DELETE 和 ON UPDATE 表示当父表发生DELETE 或 UPDATE时，子表所做的操作，可定义的子表操作有：

        CASCADE：子表做相应的DELETE或UPDATE。

        SET NULL：子表设置为null。

        NO ACTION：不允许操作，抛出错误。

        STRICT：不允许操作，抛出错误。默认配置，MySQL中同 NO ACTION

    Oracle数据库中，对于建立外键的列，一定要给该列加上一个索引，否则会导致死锁。Innodb引擎和Microsoft SQL Server一样，会在创建外键的时候自动给该列加上一个索引。

    优缺点：外键对于保证数据的参照完整性可以起到很好的作用，但是对于数据的导入操作会导致大量时间花在外键约束的检查上。因为MySQL的外键是及时检查的，所以对于每一行都会进行检查。可以在导入时关闭外键检查

 set foreign_key_checks = 0;