MYSQL之表的约束

表中真正约束字段的是数据类型, 但是只有数据类型约束就很单一, 也需要有一些额外的约束, 从而更好的保证数据的合法性, 从业务逻辑角度保证数据的正确性. 比如有一个字段是email, 要求是唯一的.

为什么要有表的约束?

• 表的约束: 表中一定要有各种约束, 通过约束, 让我们未来插入数据库表中的数据是符合预期的.
• 约束的本质是通过技术手段, 倒逼程序员, 插入正确的数据; 反过来, 站在mysql的视角, 插入到表中的数据, 一定是符合数据约束的.
• 约束的最终目标: 保证数据的完整性和可预期性.

表的约束很多, 这里主要介绍如下几个: null/not null, default, comment, zerofill, primary key, auto_increment, unique key .
它们就体现在 desc table_name; 后的下面的字段中:

---

1. 空属性 NULL/NOT NULL

C语言中 NULL 是 字面值 0, 而 mysql 中NULL指的是 “不存在”. 另外, mysql中 ‘’ 指的是空字符串.

mysql 中关于 NULL 有两个值: NULL(默认的) 和 NOT NULL(不为空)
数据库默认字段基本都是字段为空, 但是实际开发时, 尽可能保证字段不为空, 因为数据为空没办法参与运算.

创建一个myclass表, 设置前两个字段不能为null:

create table if not exists myclass(-> class_room varchar(20) not null,-> class_name varchar(20) not null,-> note varchar(20)-> );

show create table myclass 可以看到对于未指定 null 的字段note, 它帮我们补充好了default null:

插入一些数据测试一下:

1. 插入两条合法数据, 使 not null 的字段不为空, null的字段随意:

insert into myclass values("101", "高一一班", "实验班");
insert into myclass (class_room, class_name) values("102", "高一二班");

2. 我们如果要强行给 not null 的属性插入null, 会报错:

在提交一些网页的表单时, 有的字段是必须要填充的, 不填充则表单无法提交, 这类属性就是 not null 的.

2. 默认值 DEFAULT

默认值: 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好, 在需要真实数据的时候, 用户可以选择性的使用默认值.

简单来说, default如果设置了, 用户未来插入时, 有具体数据就用用户的, 没有就用默认的.

我们把NULL和DEFAULT的组合分为四类:

NULL	DEFAULT	备注
√	NULL
×	NULL
√	非NULL
×	非NULL

一. 首先看表格的前两行:

create table if not exists user_info
(gender enum('男','女'), 
name varchar(20) not null);

注意, 上面虽然Default都为NULL, 但是含义不同, 第一行是Default的值为NULL, 所以用户插入时忽略这一列默认插入NULL, 第二行是根本没有默认值, 这意味着如果用户在插入数据时忽略这一列会直接报错!.

对于第一种情况我们很熟悉, 它允许我们:

1. 手动给值(√)
2. 不给值, 默认为NULL(√)
3. 手动给NULL(√)

主要来看第二种情况:

1. 手动给值, 成功(√)
2. 不给值, 报错为 doesn’t have a default value(×)
3. 手动给NULL, 报错为 cannot be null(×)
   

这种情况意味着我们在插入时不可以忽略这一列, 一定要给一个初值.

二. 然后我们看表格的后两行:

NULL	DEFAULT	备注
√	NULL
×	NULL
√	非NULL
×	非NULL

create table if not exists user_info
(gender enum('男','女') default '男', 
name varchar(20) not null default'张三');

第三种情况, 也是允许我们:

1. 手动给值, 可以(√)
2. 不给值, 默认为default的值(√)
3. 手动给NULL(√)

insert into user_info (gender, name) values ('女', "小明");
insert into user_info (name) values ("小明");
insert into user_info (gender, name) values (NULL, "小明");

和第一种情况类似, 只不过不给值时使用的是给定的默认值.

第四种情况, 仅仅不允许我们手动插入空值:

1. 手动给值, 可以(√)
2. 不给值, 默认为default的值(√)
3. 手动给NULL, 报错(×)

从第四种情况意味着我们可以选择手动给值, 也可以用默认值, 没有像第二种情况那么强制的意思, 但是其实一般不需要同时出现.
但并不代表不能同时出现, not null 和 default 并不是想当然的: “既然有了default, 那么还要 not null 干什么, 是否多次一举?”, 不是的, 它限制了我们无法手动去插入NULL值. 看似矛盾, 实则相互补充.

3. 列描述 comment

列描述: comment 严格来说没有强制的约束意味, 它没有实际含义, 而是专门用来描述字段, 会根据表创建语句保存, 用来给程序员或DBA来进行了解

create table t12( name varchar(20) comment '姓名', age int unsigned comment "年龄" 
);

desc t12 查看不到comment的信息:

通过show可以看到:

4. 零填充 zerofill

我们已经了解过了 float, decimal, char, varchar, enum, set 后面带圆括号分别是什么含义了, 但没有提过整型后面的圆括号内的数字是什么含义. 它的名字叫作显示宽度
zerofill 和 显示宽度密切相关. 比如 int(10) 代表什么意思呢? 整型不是4字节码? 这个 10 又代表什么呢？其实没有zerofill这个属性, 显示宽度是毫无意义的.

create table t14 (a int unsigned,b int(11) unsigned
);

这里我们故意把 b 设置为int (11), 但是无论是 desc 还是 show 都无法显示, 这也说明了没有 zerofill 约束的整型, 显示宽度是无意义的:


我们给 t14 的这两个 int 属性都添加上zerofill约束:

alter table t14 modify a int unsigned zerofill;
alter table t14 modify b int(11) unsigned zerofill;

再次查看, 可以看到显示宽度, 而且 int 的显示宽度默认是 10, 因为 int 表示的数据范围最大单位是十亿, 也就是10位十进制数字.

现在插入三条数据:

insert into t14 values (1, 2);
insert into t14 values (100, 200);
insert into t14 values (100000, 200000);

由于显示宽度的约束, 这意味着未来我们显示出来的表格将会是等宽的, 最终显示的长度是 max(数据本身长度, 圆括号内的长度).

现在我把 a 的显示宽度设置为 1, 此时表中的所有 a 都满足max(数据本身长度, 圆括号内的长度) = 数据本身的长度

再次查看:

5. 主键 PRIMARY KEY

主键定义: primary key用来唯一的约束该字段里面的数据, 不能重复, 不能为空, 一张表中最多只能有一个主键(但是一个主键可以是一列或多列 ).

1. 创建主键属性:

主键所在的列通常是整数类型.

create table t15(
-> stu_id int unsigned primary key comment '学号不能为空',
-> stu_name varchar(20) not null
-> );

通过 show 可以看出它自动帮我们把 “列级约束” 抽离出来作为“表级约束”来存储, 这样也是可以的:

我们还可以发现, MYSQL还会自动把主键设置为 NOT NULL 的, 即使当时定义时没有显示说明.

desc 也可以看到 stu_id 在 Key 列有主键标识:

也可以删除主键, 追加主键(要追加主键必须当前表没有主键):

alter table t15 drop primary key; //删除当前表的主键
alter table t15 add primary key(stu_id); //追加主键

2. 与主键相关的插入事项

insert 插入的一行元组中, 主键对应的字段中不能重复:

insert into t15 values (1, "zhangsan");
//插入下面这行会报错, 因为主键对应的字段中不能重复!
insert into t15 values (1, "lisi"); 
ERROR 1062 (23000): Duplicate entry '1' for key 't15.PRIMARY'

3. 复合主键

涉及复合主键, 我们就要使用"表级约束", 方法是在创建表的时候, 在所有字段之后, 使用 primary key(主键字段列表) 来创建主键, 如果有多个字段作为主键, 可以使用复合主键.

• 列级约束适合定义简单规则；
• 表级约束适合定义复合约束，或统一写在表尾以保持清晰结构；

MySQL 最终会将所有约束都转化为表级约束存储和显示

create table t16 (
-> id int unsigned,
-> course char(10),
-> score tinyint unsigned,
-> primary key(id, course)
-> );

插入几条数据:

insert into t16 (id, course) values (1, '高等数学', 100);
insert into t16 (id, course, score) values (1, '高等数学', 100);
insert into t16 (id, course, score) values (1, '英语', 100);
insert into t16 (id, course, score) values (2, '高等数学', 90);

重复插入(1, ‘高等数学’) 会主键冲突:

insert into t16 (id, course, score) values (1, '高等数学', 80);
ERROR 1062 (23000): Duplicate entry '1-高等数学' for key 't16.PRIMARY'

6. auto_increment 自增长

auto_increment: 当对应的字段, 不给值, 会自动的被系统触发, 系统会从当前字段中已经有的最大值 +1操作, 得到一个新的不同的值. 通常和主键搭配使用, 作为逻辑主键.

自增长的特点:

• 任何一个字段要做自增长, 前提是本身是一个索引(key一栏有值)
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

create table t17 (
-> id int unsigned primary key auto_increment,
-> name varchar(20)
-> );

可以发现extra一栏有了auto_increment约束:

然后我们在插入时可以忽略 id , 因为 id 字段可以自增长:

insert into t17 (name) values ("a");
insert into t17 (name) values ("b");
insert into t17 (name) values ("c");

show create table 可以发现 auto_increment 字段的值为 4, 正是下一个要插入的字段的值:

也就是说我们想办法修改auto_increment 值, 就可以改变下一次插入时自增长字段的值, 有两种方法:

1. 隐式修改, 手动插入一条影响 id 的值:

insert into t17 (id, name) values (500, "c");
insert into t17 (name) values ("d");
insert into t17 (name) values ("e");

2. 直接修改auto_increment字段:

//创建新表时直接约束
create table t18 (
-> id int unsigned primary key auto_increment,
-> name varchar(20)
-> )auto_increment=1000;//修改旧表的 auto_increment 值
alter table t17 auto_increment=1000;

修改之后再插入就从新的自增值继续自增了.

补充: 通过 last_insert_id 获取上次插入的 AUTO_INCREMENT 的值:

7. 唯一键

一张表中有往往有很多字段需要唯一性. 而一张表中只能有一个主键, 可以有多个唯一键, 唯一键就可以解决表中有多个字段需要唯一性约束的问题.

1. 例子1

概括的来讲: 主键用于标识一个表中 “这一行是谁”, 而唯一键用于保证 “某个业务值不会重复”, 比如:

CREATE TABLE citizen (id INT AUTO_INCREMENT PRIMARY KEY,      -- 系统内部主键id_card CHAR(18) UNIQUE,                -- 公民身份证号：必须唯一phone VARCHAR(11) UNIQUE,               -- 手机号：也要唯一name VARCHAR(50)
);

身份证号和手机号在业务逻辑上都是需要唯一的, 不是说表中只能有一个主键, 其它属性的唯一性就无法去确定了. 所以主键无法同时约束多个字段唯一, 唯一键正好补上.

2. 例子2

我们知道表的主键可以由多个列构成, 那我们是否可以将多个唯一的属性合并为一个主键呢?

这就要提出主键和唯一键在技术上的区别: 主键不能为NULL, 而唯一键可以为NULL.

CREATE TABLE users (user_id INT PRIMARY KEY,login_name VARCHAR(30) UNIQUE NOT NULL,email VARCHAR(100) UNIQUE
);

1. 先看向 email 字段, 不是每一个人都需要填写email, 从表的角度来说就是即使是唯一键, 也允许它为NULL, 而主键是不允许为NULL的.
2. 那么我们需要一个不为 NULL 的唯一键, 就在其后面加上约束即可 .

因此我们可以发现: 主键和唯一键是互相补充的, 主键保证表中的一行元组的唯一性, 二唯一键保证本列信息在业务逻辑中不会与其它信息重复.

总结:
我们的一张表存储的各个属性列是为了去描述一个对象的. 而这些属性列中, 我们可以选择其中的 某个/些 属性去充当主键, 用于标识对象的唯一性; 但是并不排除这个对象中仍然有其它的属性的唯一性仍然需要我们去维护, 这样的属性就是唯一键.
MYSQL 为了维护属性一定是要让其符合现实逻辑的, 所以唯一键的存在就是为了让创建出来的表约束力更强, 防止上层用户的一些误操作, 它不能保证用户一定不犯错误(即使保证了唯一性, 但是输入错误), 但是能从技术层面在语义上保证这个属性一定是唯一的.

主键是逻辑结构的核心标识, UNIQUE 是业务层面补充约束.

8. foreign key 外键

首先一个数据库中的数据并不是都只存在一张表中, 而是要遵循 “规范化设计” 分成多个表来存储.

比如这里有三张表 customer goods 和 purchase:

 create table customer( customer_id int unsigned primary key auto_increment, name varchar(20) not null comment "客户姓名", address varchar(20) comment "客户地址," , email varchar(30) unique comment "客户邮箱", sex enum('男','女') default '男' not null, card_id varchar(50) not null unique comment "客户身份证" 
);create table goods (goods_id int unsigned NOT NULL AUTO_INCREMENT,goods_name varchar(32) NOT NULL COMMENT '商品名称',unitprice decimal(5,2) DEFAULT '0.00' COMMENT '商品价格',category varchar(12) DEFAULT NULL COMMENT '商品分类',provider varchar(12) NOT NULL COMMENT '供应商',PRIMARY KEY (goods_id)
);create table purchase (order_id int unsigned NOT NULL AUTO_INCREMENT,customer_id int unsigned DEFAULT NULL COMMENT '客户编号',goods_id int unsigned DEFAULT NULL COMMENT '商品编号',nums int unsigned DEFAULT '0' COMMENT '购买数量',PRIMARY KEY (`order_id`),FOREIGN KEY (customer_id) REFERENCES customer (customer_id),FOREIGN KEY (goods_id) REFERENCES goods (goods_id)
);

如果我们在订单purchase表中去维护customer和good的所有信息, 这样会造成:

1. 数据冗余, 重复的信息被多次存储, 浪费空间.
2. 不便于维护和扩展, 如果customer的信息变了, 只需更新一次 customer 表, 而不是全局修改.

外键的作用在多表中得以体现, 外键是一种约束, 目的是保证多个表之间的数据一致性, 在purchase表中主要体现在这两行:

FOREIGN KEY (customer_id) REFERENCES customer (customer_id),
FOREIGN KEY (goods_id) REFERENCES goods (goods_id)

外键的要求:

1. 引用目标字段必须是 PRIMARY KEY 或 UNIQUE：

2. 引用字段和被引用字段类型必须严格一致: 如果主表中被引用字段是 INT UNSIGNED, 那么从表中被引用字段类型也要是 INT UNSIGNED.

3. 长度, 符号(如是否 UNSIGNED）、字符集（对于字符串）也必须相同。

主要作用:

• 主表中的主键存在时(比如customer_id, goods_id), 引用它的从表才能插入该值, 在这个例子中体现为 “不能在订单中引用不存在的顾客或商品”.
• 实现级联删除 / 更新

比如现在有customer和goods有这些数据:

所以插入 顾客1-3 和 商品1-3 的任意组合都是合法的:

insert into purchase (customer_id, goods_id, nums) values (1, 1, 2);
insert into purchase (customer_id, goods_id, nums) values (1, 3, 1);
insert into purchase (customer_id, goods_id, nums) values (1, 3, 1);
insert into purchase (customer_id, goods_id, nums) values (3, 2, 2);

但是一旦我们向从表(purchase)中插入主表中不存在的数据, 外键的约束就会触发错误:

mysql> insert into purchase (customer_id, goods_id, nums) values (4, 2, 2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bit32mall`.`purchase`, CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`))

由于我们的purchase表中引用了张三(id为1), 所以我们试图从主表(customer)中删除从表已引用的元素, 也会由于外键的约束而报错 (除非设置 ON DELETE CASCADE 级联删除):

mysql> delete from customer where name='张三';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bit32mall`.`purchase`, CONSTRAINT `purchase_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`))

但是李四(id=2)始终没有在从表中被引用, 因此可以正常删除:

mysql> delete from customer where name='李四';
Query OK, 1 row affected (0.00 sec)

上面两种外键约束的情景, 都保证了mysql中对表中数据进行插入和删除时的数据一致性.

总结:
明显customer, goods, purchase 三张表在业务上是有相关性的, 但是如果在业务上没有建立约束关系(外键约束), 那么就可能出现问题(数据不一致).
而解决方案就是通过外键完成的. 建立外键的本质其实就是把相关性交给mysql去审核了, 提前告诉了 mysql 表之间的约束关系, 那么当用户插入不符合业务逻辑的数据的时候, mysql 就不允许你插入, 从而倒逼程序员在插入数据时需要做好数据的完整性约束.