mysql外键的基本概念_SQL之MySQL主外键基本概念和总结

目录

1. 前言

2. 键的分类

2.1 主键

2.2 主键约束

2.3 外键

2.4 外键约束

2.5 总结

3. 添加键的方法

3.1 创建主键

3.2 创建外键

3.3 增删主键

3.4 增删外键

4. 设计键的原则

5. 常见的表键应用(主键和外键的级联操作)

1.前言

上一次，我总结了数据库的索引知识点，接下来则是对数据库的键进行总结，而且键与索引又存在着一定的关系，因此，我们很有必要对齐进行掌握。

2.键的分类

2.1 主键

主键是某一行属性或者属性组的唯一标示表。

一个表有且只能有一个主键

保证记录的唯一和主键域的非空

主键也是一个特殊的唯一索引

而主键又分为自然主键和代理主键：

自然主键->该字段除了表示该行唯一之外,存储的数据还有其他的作用,比如身份证(非空唯一)；

代理主键->只起到标识唯一的作用,一般自增生成,没有其他特定含义；

另外,主键也可以是几列一起形成联合主键。

2.2 主键约束

数据库自动对主键进行约束,即唯一非空。

2.3 外键

外键是用来建立和加强两个表数据之间的连接关系。

表的外键通常是另一张表的主键，而外键就是将两表的关系联系起来，通常我们把有外键字段的表成为从表，与之有对应关系的表称为主表。一般来讲，删除一张表的主键必须保证其他表没有与之关联的外键才能够保证数据库的稳定。

PS：外键使用条件必须主外键表都为INNODB引擎，外键列必须建立索引(4.1.2版本后在建立外键会自动创建索引)，外键关系两表的列必须数据类型相似。

2.4 外键约束

外键约束是用来维护两张表之间的数据一致性的，mysql添加外键约束后,如果主表中的主键与从表的外键产生关联,主键就不能删除。

2.5 总结

主键、外键与索引三者区别

主键

外键

索引

定义

某一列记录的唯一标识，不能重复，不允许为空

表的外键是另一张表的主键，外键可以有重复，也可以为空值

索引不允许有重复值，但允许为空

作用

保证数据完整性

与其他表产生联系

提高查询的速度

个数

主键有且只能有一个

一个表可以有多个外键

一个表可以有多个唯一索引

主键一定是唯一索引，但唯一索引不一定就是主键

一个表可以有多个唯一索引，但只能有一个主键

主键列不允许为空，但其他的唯一索引允许为空值

主键可以作为外键引用，但其他索引不能作为外键引用

3.添加键的方法

3.1 创建主键

通常通过“primary key”命令设置主键，如下所示：

create table XXX(

id int(11) not null primary key auto_increment,

name char(10)

);

或者

create table XXX(

id int(11) not null auto_increment,

name char(10),

primary key(id)

);

3.2 创建外键

通过[constraint 外键ID名] foreign key(外键名) references 外键表(外键名)，比如：

create table a(

id int(11),

userid int(11),

[constraint fk_userid] foreign key(userid) references user(id),

primary key(id)

);

--[外键名称] 用于删除外键约束的，一般建议“fk”开头或结尾

3.3 增删主键

alter table 表名 modify 主键列名 新列类型(不含auto_increment);

--先删除自增，才能删除主键

alter table 表名 drop primary key;

--删除主键

alter table 表名 add primary key(列名);

--添加主键

3.4 增删外键

alter table 子表名 add [constraint fk_sno] foreign key(子表的外键名称) references 父表名(父表的主键名称);

--添加外键约束。fk_sno为外键ID名，若不添加，系统会自动配一个。

alter table 子表名 drop foreign key fk_sno;

--删除外键约束。fk_sno为外键ID名, 若不知，可查询建表明细(show create table 表名)。

4.设计键的原则

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。

主键的用途：

唯一性标识一行数据

可作为一个被引用的外键对象

因为主键的用途，因此设置主键应当遵循下面的一些规则：

主键应当是没有意义的，比如每个表的自增ID。

主键应该是单列的，这样可以提高查询的效率。

主键一旦创建，就不应该更新主键的数据。

主键不应该包含动态变化的数据，比如时间戳。

主键应当由计算机自动生成。

5.常见的表键应用(主键和外键的级联操作)

场景：

一个商品表下有某个商品系列属性，比如，该商品有个对应的商品产地属性，而该产地属性又存在订单表里，如果此时操作员发现，某个商品的产地属性数据有误，希望修改了商品表的产地属性能够通过自动更正订单表对应属性的值，那么此时就是定义外键的魅力所在。

在定义外键的时候，在最后加入这样的关键字：ON UPDATE CASCADE;即在主表更新时，子表(们)产生连锁更新动作，有人喜欢把这个叫“级联”操作。

比如：

alter table 子表名 add [constraint fk_sno] foreign key(子表的外键名称) references 父表名(父表的主键名称) ON UPDATE CASCADE;

注意：

除了UPDATE外还有DELETE操作；除了CASCADE 外，还有 RESTRICT(禁止主表变更)、SET NULL(子表相应字段设置为空)等操作

具体SQL如下所示：

[on delete {restrict | cascade | set null | on action | set default}]

[on update {restrict | cascade | set null | on action | set default}]

on delete,on update表示事件触发限制，可设参数：

restrict(限制外表中的外键改动)

cascade(跟随外键改动)

set null(设空值)

set default(设默认值)

no action(无动作，默认的)