• MySQL——外键约束

    背景:

    外键的存在意义:    让数据库自己通过外键来保证数据的完整性和一致性

    外键的使用引擎:  InnoDB

    外键的使用方式:  如果不能很好的掌握外键的使用方式,那么外键可能不会为你带来方便,反而会造成很多麻烦,例如删除失败等。

    基本概念:

    什么是外键? 两者之间的关系,我想引用下面的一个例子就会一幕了然:

    首先,我有一张大哥表,这里面记录的都是黑白两道有头有脸的人物:

    yy

    然后,也有一张小弟表,记录了上面那些大哥的小弟们

    yy

    现在,大哥陈浩南收了一个小弟xinyu,也就是我,嘿嘿。

    yy

    有一天,有人要砍我大哥。靠,那怎么能忍呢?  要砍先砍我,除非从我身上踏过去!

     

    结果,人家真的先把我砍了,然后再把我大哥砍了。。。

    yy

    然后又来了重新来了一个大哥山鸡。这时候,却来了一个浩南哥的粉丝想要加入:

    yy

    山鸡当然不同意了,浩南已经被他砍了。现在是他在当家做主

    上面的例子总结下来就是:

    1. 当删除父表的行时,必须先将对应的子表中的行全删除了才能删除成功,否则报错。

    2. 当插入子表时,子表的外键列输入的值,只能是父表外键关联列已有的值.否则报错。

    具体操作:

    一. 添加外键的限制:

    1. InnoDB 引擎,这里比较操蛋的是MyISAM也可以添加成功,但其实只是起了个名字而已,并没有什么卵用。

    yy

    2. 设置为外键的字段size和type都必须相同。 (MyISAM类型不相同也可以设置成功,然并卵!!!)

    3. 所有要建立外键的字段必须建立索引。

    二.  添加外键的格式

    yy

    ON DELETE / ON UPDATE 用于定义与父表的delete和update操作关联,以下是update,delete操作的各种约束类型:

    • CASCADE:当父表中外键字段值被更新或删除时,所在的子表中所有行的对应的字段也会被更新或删除。这里很容易忽视,当你的外键索引不是唯一约束的话,那么父表中的某一行的字段的变化,可能会引起子表中很多行的字段跟着变化
    • RESTRICT:相当于no action,即不进行任何操作。也就是说不会随着父表的变化而变化,这时候外键约束还是起作用的(比如你要砍大哥,小弟是不允许的),只不过子表不会随着父表的变化而变化。 这是默认值!
    • SET NULL :当父表的外键关联字段被update,delete时,子表的外键值被置为null

    三. 查看和删除外键:

    查看外键:

    方式1: show create table “table_name”;

    方式2 : 查看INFORMATION_SCHEMA.KEY_COLUMN_USAGE表

    yy

    删除外键: alter table “table_name” drop foreign key “key_name”;

    四. 调整外键参数:foreign_key_checks:

    foreign_key_checks = 0 表示禁用外键约束;

    foreign_key_checks = 1 表示开启外键约束;

    show global variables like “foreign_key_checks”,可查看此值得状态。

    当我们用mysqldump程序导出一个具有外键的子表时,myskdump程序就会在导出的数据文件中加入一条语句,将foreign_key_checks设置为0,。也就是说,当你在reload的时候,那条语句就会起作用,将foreign_key_checks设置为0。

    yy

    我们将兄弟这张表mysqldump出来,大家可以看到在最上方,先将旧的foreign_key_checks保存,然后再设置为0。 等创建完表,并且插入数据后,再将foreign_key_checks设置为原来的值。其实这样也就相当于:

     

    五. 查看foreign key的错误:

    方式1: 我们可以通过MySQL的错误日志来查看。

    方式2: 可以用show engine innodb status 将innodb monitor监控到的最近一次发生的外键错误显示出来:

    yy

    官方文档地址:

    http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html