mysql 外键简介

MySQL有两种常用的引擎类型:MyISAMInnoDB。目前只有InnoDB引擎类型支持外键约束。

 

外键的功能也是开发友好型,DBA不友好型。同样外键所提供的功能也是属于业务逻辑。外键的存在对数据库的性能损耗也是巨大的、对数据库的日常维护也是不友好的,更易造成死锁的问题,也不易于后期数据库架构的扩展。对于并发量较大的应用,强烈不建议使用外键约束。当然对于性能要求不高,而且开发人员较少的情况下(主要降低开发人员的代码量,加快项目进度)可以使用外键。

 

 

1、外键是保持数据完整性和一致性的机制。

举例子加深理解:

user 表:id 为主键

profile 表: uid 为主键

简单来说,若表 profile uid 列 作为表外键(外建名称:user_profile),以表 user 做为主表,以其 id列 做为参照(references),且联动删除/更新操作(on delete/update cascade)。则 user表 中删除 id 1 的记录,会联动删除 profile uid 1 的记录。user 表中更新 id 1 的记录至 id 2,则profile 表中 uid 1 的记录也会被联动更新至 uid 2,这样即保持了数据的完整性和一致性。

 

 

 

 

2InnoDB中外键约束定义的语法如下

ALTER TABLE tbl_name

    ADD [CONSTRAINT [symbol]] FOREIGN KEY

    [index_name] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name,...)

    [ON DELETE reference_option]

    [ON UPDATE reference_option]

 

举例子:

添加外键#

alter table  my_student

add constraint `student_fk` foreign key (cid)

references my_class(id) on delete cascade on update cascade;

删除外键#

alter table my_student drop foreign key `student_fk`;

 

 

 

3、使用注意事项:

*表引擎必须为InnoDB

*外键必须建立索引(可以为普通、主键、唯一,事先不建立的话会自动创建一个普通索引),你要用的外键和参照的外表的键都要加索引

*添加前确认 子表中所有记录的外键值,都能在主表中有对应。(即主表要包含子表,如果子表中有主表中不存在的对应值,则添加外键失败)

*建立外键关系的对应列必须具有相似的InnoDB内部数据类型(数据类型相近,最好一致)

 

 

 

 

 

 

4、验证外键理论,外键为保证数据的完整性和一致性采用的四类动作

 

* CASCADE: 父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。ON DELETE CASCADEON UPDATE CASCADE都被InnoDB所支持。

 

* SET NULL: 父表中删除或更新对应的行,同时将子表中对应行的外键列设为NULL。(注意,此项在外键列没有被设为NOT NULL时有效,否则连建立外键都不允许)。ON DELETE SET NULLON UPDATE SET SET NULL都被InnoDB所支持。

 

* NO ACTION: 父表中删除或更新对应的行,如果子表中存在对应的行,则InnoDB拒绝删除或者更新父表。

 

* RESTRICT: NO ACTION动作

 

举例子验证:

##建子表

  create table my_student(

`id` int unsigned not null auto_increment primary key,

`name` varchar(25) not null comment 'student name',

`cid` int unsigned default null comment 'student class id',

key `cid`(`cid`)

)engine=InnoDB default charset=utf8 auto_increment=1;

 

##建主表

create table my_class(

`id` int unsigned not null auto_increment primary key,

`info` tinytext not null default ''

)engine=InnoDB default charset=utf8 auto_increment=1;

 

##加数据

insert into my_student set name='小王',cid=1;

insert into my_student set name='小明',cid=2;

insert into my_class set  info='一年级';

insert into my_class set  info='二年级';

 

 

##加外键

alter table  my_student

add constraint `student_fk` foreign key (cid)

references my_class(id) on delete cascade on update cascade;

 

 

 

##进行测试,更新和删除。

update my_calss  set id =?  where id=?

delete from my_calss where id=?

 

 

 

 

##删除外键

alter table my_student drop foreign key `student_fk`;

 

 

 

 

##验证结果

正常的测试结果和上边四类动作描述的一样(CASCADE SET NULL、  NO ACTION、  RESTRICT)。

对于主表的增加(insert)操作没什么限制,对于主表的deleteupdate则根据四类动作的设定来动。

对于子表的增加(insert)操作,主表中必须提前有外键对应的值才可以,否则不允许插入。deleteupdate操作,对于子表来说不受限制。

对于主表update或者delete操作,若子表中不存在对应的记录。操作正常进行,不受影响。

 

 

 

##如果想跳过外键约束

mysql> SET FOREIGN_KEY_CHECKS = 0; ##跳过外键约束

 

some action

 

mysql>SET FOREIGN_KEY_CHECKS = 1; ##恢复外键约束

 


##查看数据库中所有外键:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME is not null\G

 

##具体的外键定义查看下

show create table table_name;


 

 

https://www.cnblogs.com/yzuzhang/p/5174720.html

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页