MySQL 表约束详解
一、为什么要有表的约束?
在上一篇文章中,我们认识了很多的数据类型,并在它们的下面通过例子进行了演示。在这些例子中,我们向表中插入了一些不合法或超出范围的数据。
MySQL 表约束用于确保数据完整性与正确性。主要包含空属性(null/not null)、默认值(default)、列描述(comment)、零填充(zerofill)、主键(primary key)、自增长(auto_increment)、唯一键(unique)及外键(foreign key)。通过约束机制,数据库可拦截非法数据插入,维护表间关系。例如主键保证唯一非空,外键维护关联表一致性。掌握这些约束是 MySQL 基础操作的关键。

在上一篇文章中,我们认识了很多的数据类型,并在它们的下面通过例子进行了演示。在这些例子中,我们向表中插入了一些不合法或超出范围的数据。

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online
结果可以看到,MySQL 会拦截我们的操作。这是因为我们要插入的数据不符合当前数据类型的约束。只要插入的数据不在约束之内,MySQL 就会进行拦截。
为什么要有表的约束?
答案是通过约束,可以保证未来插入数据库表中的数据是符合预期的。约束的本质就是通过技术手段,倒逼程序员插入正确的数据。站在 MySQL 的角度,凡是插入进来的数据,都是符合数据约束的。
单单只通过数据类型来进行约束是不够的,还需要有更多的约束条件。
一般有两个值:null(默认)和 not null(不为空)。
与之相关的其实就是使用 desc 指令查看表的属性中 Null 这一列,默认情况下这一列的内容都是 YES,表示该属性数据可以为空。
如何让一个属性的数据不能为空?在表的属性后面加上 not null 即可将该属性设置为 not null。设置后再次通过 desc 指令查看,Null 这一列的内容将变为 NO。
验证:当尝试插入 NULL 时,MySQL 会拦截并报错,提示该列不能为空。
默认值:某一种数据会经常性地出现某个具体的值,可以在一开始就指定好,在需要真实数据的时候,用户可以选择性地使用默认值。
默认值可以认为是 C++ 语法中的缺省值,当我们没有传入该参数的实参时,就会使用声明时所设定的缺省值。
在表的属性中,Default 这一列表示的就是默认值。设置默认值的方法是在属性后面加上 default + 默认值。
如果插入数据时没有显式传入该字段,系统会使用创建表时设置的默认值。
关于 not null 和 default 是否冲突:它们是不冲突的,反而互补。
not null:前提是要插入数据,但插入的数据不能为空。default:如果不向表中插入数据,则使用默认值。MySQL 支持 not null + default 的写法。如果要插入数据,则不能为空;如果不插入数据,则使用默认值。
列描述:comment,没有实际含义,专门用来描述字段,会根据表创建语句保存,用来给程序员或 DBA 了解。
通过 show 指令可以查看一个表被创建的相关语句,从而看到每个属性的 comment 描述。
列描述并没有像 not null、default 那样在不满足条件时拦截报错,只是给程序员描述该属性是什么含义。
zerofill 关键字用于控制整数显示格式。
当通过 show 指令查看创建表信息时,int 类型后面跟了一个 (10),代表宽度为 10。
如果在属性类型后面加上 zerofill 关键字,向其中插入数据后,查看表中的数据,发现数据会自动补零。例如 123 变为 0000000123。
原因正是因为 zerofill 起作用了:如果插入的数据宽度小于指定宽度,会在前面自动补 0。
注意:这只是最后显示的结果,实际在 MySQL 中存储的依旧是原始数值。
如果插入的数据宽度超过了 int 括号中的数字,就不再自动补 0,效果等同于不加 zerofill。
补充:设置 unsigned 表示无符号,去掉后变为有符号,因此括号中的数字会从 10 变为 11(多一个符号位)。
主键:primary key 用来唯一地约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键;主键所在的列通常是整数类型。
设置方式很简单,在属性的后面加上 primary key 即可。
如果创建表时没有设置主键,后续可以通过 alter 指令添加或删除主键。
alter table 表名 add primary key (属性名);alter table 表名 drop primary key;复合主键就是在创建表的时候,在所有字段之后,使用 primary key(主键字段) 来创建主键。如果有多个字段作为主键,可以使用复合主键。
设置复合主键的方式是在创建表的最后加上 primary key(属性名)。
如果设置了复合主键,只有当全部属性都重复了才会触发主键不能重复的机制,那时 MySQL 才会拦截。
auto_increment:当对应的字段不给值,会自动地被系统触发,系统从当前字段中已经有的最大值 +1 操作,得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。
特点:
查看自增长起始值可通过 show 指令获取 AUTO_INCREMENT 信息。
一张表中往往有很多字段都需要唯一性,数据不能重复,但是一张表中只能有一个主键。保证其他字段的唯一性可以通过唯一键实现。
唯一键和主键差不多,但是唯一键允许为空,而且可以多个为空,空字段不做唯一性比较。
MySQL 是关系型数据库,体现在表与表之间的关系。用于表与表之间关系的就有外键。
外键用于定义主表和从表之间的关系:外键约束主要定义在从表上,主表则必须是有主键约束或 unique 约束。当定义外键后,要求外键列数据必须在主表的主键列存在或为 null。
为了维持这种合理的外键关系,需要通过外键约束来实现。如果插入的数据在主表中不存在,MySQL 会拦截。
外键约束其实是双方互相约束:主表约束从表不能无中生有,而从表约束主表不能'始乱终弃'。