MySQL学习之路：数据的完整性-外键约束
数据的完整性
数据完整性是指数据的准确性和逻辑一致性，用来防止数据库中存在不符合语义、无效数据或错误数据等。例如，网上商城系统数据库中的商品编号、名称不能为空，订单号必须唯一，邮箱格式必须符合规范等。MySQL中，数据完整性通常使用约束来实现，本任务主要的约束包括PRIMARY KEY约束、NOT NULL约束、DEFAULT约束、UNIQUE约束、CHECK约束和FOREIGH KEY约束。
PRIMARY KEY
又称为主键约束，定义表中构成主键的一列或多列。 主键用于唯一标识表中的每条记录，作为主键的字段值不能为NULL且必须唯一，可以是单一字段，也可以是多个字段的组合。 每个数据表中最多只能有一个主键约束。
在Navicat中创建商品表goods。
序号 | 字段 | 数据类型 | 主键 | 允许空 | 说明 |
|---|---|---|---|---|---|
1 | gid | int | 是 | 否 | 商品id |
2 | gname | varchar(200) | 否 | 商品名称 | |
3 | gprice | decimal(20,2) | 否 | 价格 |
使用PRIMARY KEY关键字设置主键约束
主键约束由关键字PRIMARY KEY标识。
字段名 数据类型 PRIMARY KEY使用SQL语句，创建商品表goods，并设置gid列为主键。
mysql> CREATE TABLE goods
-> (gid int PRIMARY KEY, #标识该字段为主键
-> gname varchar(30) NOT NULL,
-> gprice decimal(20,2)
-> );
Query OK, 0 rows affected (0.03 sec)当主键由多个字段组合构成时，主键只能在字段定义完成后设置。
PRIMARY KEY(字段名1，字段名2，…，字段名n)
【例3.36】创建购物车表car。序号 | 字段 | 数据类型 | 主键 | 允许空 | 说明 |
|---|---|---|---|---|---|
1 | gid | int | 是 | 否 | 商品id |
2 | uid | int | 是 | 否 | 用户id |
3 | cnum | int | 购买数量 |
mysql> CREATE TABLE cart
-> (gid int,
-> uid int,
-> cnum int,
-> PRIMARY KEY(gid,uid) #定义复合主键
->);
Query OK, 0 rows affected (0.02 sec)NOT NULL约束也称非空约束 强制字段的值不能为NULL，它不等同于0或空字符串，也不能跟任何值进行比较。 NOT NULL只能用作约束使用。
属性名 数据类型 NOT NULL为商品goods添加字段gcode（商品编号），类型为varchar(50)，不为NULL，并将其放置gid字段之后。
mysql> ALTER TABLE goods
-> ADD gcode varchar(50) NOT NULL AFTER gid ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0DEFUALT 约束
DEFAULT约束即默认值约束，用于指定字段的默认值。 当向表中添加记录时，若未为字段赋值，数据库系统会自动为将字段的默认值插入。
属性名 数据类型 DEFAULT 默认值修改购物车表cart，将购买数量的默认值设置为1。
mysql> ALTER TABLE cart
-> MODIFY cnum int DEFAULT 1 ; #修改默认值为1
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0UNIQUE约束
UNIQUE约束又称唯一性约束，是指数据表中一列或一组列中只包含唯一值。
属性名 数据类型 UNIQUE修改users_new表，为字段登录名ulogin添加UNIQUE约束。
mysql> ALTER TABLE users_new
-> MODIFY ulogin varchar(50) UNIQUE ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0CHECK约束
CHECK约束是列输入数据值的验证规则，列中输入数据必须满足CHECK约束的条件，否则无法写入数据库。 MySQL 8.0开始支持CHECK约束。
CONSTRAINT 约束名 CHECK ( 表达式 )修改goods表，为商品价格添加CHECK约束，要求价格必须大于0等于。
mysql> ALTER TABLE goods
-> ADD CONSTRAINT ck_gprice CHECK(gprice >= 0) ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0FOREIGN KEY约束
用Navicat图形工具创建外键约束
创建商品类型表category和商品goods表，category表结构如表3-10所示，goods表结构如表3-11所示。其中主表为category，从表为goods。
序号 | 字段 | 数据类型 | 主键 | 外键 | 允许空 | 说明 |
|---|---|---|---|---|---|---|
1 | cid | int | 是 | 否 | 类别id | |
2 | cname | varchar(30) | 否 | 类别名称 |
序号 | 字段 | 数据类型 | 主键 | 外键 | 允许空 | 说明 |
|---|---|---|---|---|---|---|
1 | gid | int | 是 | 否 | 商品id | |
2 | cid | int | 是 | 否 | 类别id | |
3 | gcode | varchar(50) | 否 | 商品编号 | ||
4 | gname | varchar (200) | 否 | 商品名称 | ||
5 | gprice | decimal(20,2) | 商品价格 |
使用SQL语句添加外键约束
CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 主表名(主键字段名)mysql> ALTER TABLE goods
-> ADD CONSTRAINT fk_goods_cid FOREIGN KEY(cid) REFERENCES
category(cid);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0外键约束的级联更新和删除 外键约束实现了表间的引用完整性，当主表中被引用列的值发生变化时，为了保证表间数据的一致性，从表的中与该值相关的信息也应该相应更新，这就是外键约束的级联更新和删除。
CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 主表名(主键字段名)
[ON UPDATE { CASCADE | SET NULL | NO ACTION | RESTRICT }]
[ON DELETE { CASCADE | SET NULL | NO ACTION | RESTRICT }]- CASCADE：指定在更新和删除操作表中记录时，如果该值被其他表引用，则级联更新或删除从表中相应的记录。
- SET NULL：更新和删除操作表记录时，从表中相关记录对应的值设置为NULL。
- NO ACTION：不进行任何操作
- RESTRICT：拒绝主表更新或修改外键的关联列。
删除约束
- 使用DROP TABLE语句删除表时，表中所有的约束也随之被删除。
- 使用ALTER TABLE可以删除指定的约束。
ALTER TABLE 表名 DROP 约束类型 [约束名];