数据库应用（mysql）完整性约束

第1关：主键约束

任务描述

本关任务：
1.创建一个tmp表，并查看tmp表结构，tmp表结构如下：

2.创建一个stock（库存)表，并查看表结构，stock表结构如下：

USE petstore;   #跳转数据库
create table tmp   #创建表
(
userid char(10) not null,
fullname char(50) not null,
phone char(11) not null,
primary key(userid));  #创建主键
desc tmp;      #查看表结构
create table stock   #创建表
(
shelfid char(10) not null,
workerid char(10) not null,
productid char(50) not null,
num int(8) not null,
primary key(shelfid,productid));  #创建主键
desc stock;      #查看表结构
############Begin#########
##########End##########

####相关知识

为了完成本关任务，你需要掌握：定义主键

语法

1.主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。由于PRIMARY KEY约束能确保数据的唯一，所以经常用来定义标志列。当为表定义PRIMARY KEY约束时，MySQL为主键列创建唯一性索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果 PRIMARY KEY 约束是由多列组合定义的，则某一列的值可以重复，但 PRIMARY KEY 约束定义中所有列的组合值必须唯一。
可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARY KEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY（col_name，…）语句。
2.查看表结构
desc 表名;

 

第2关：替代键约束

任务描述

本关任务：1.新建一个收银员表（checker），表结构如下：

其中收银员id为主键，身份证号码idcard为替代键。
2.查看checker表结构

USE petstore;   #跳转数据库
create table checker   #创建表
(
id char(10) not null,
name char(50) not null,
idcard char(11) not null,
primary key(id),     #创建主键
unique(idcard)       #创建代替键
); 
desc checker;      #查看表结构
########Begin########
########End########

####相关知识
1.在关系模型中，替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。
2.用“ desc 表名;”查看表结构

第3关：参照完整性约束

任务描述

本关任务：
1.订单拥有订单详情。订单表orders是主表，订单详情表lineitem是子表，外键是订单号orderid。为订单详情表lineitem定义外键orderid，通过外键参照订单表orders，并保证更新订单表时，订单详情表自动更新；只要某个订单在订单详情表中还存在记录时就拒绝删除对应的订单信息。
2.查看lineitem创建表的信息

USE petstore;   #跳转数据库
alter table lineitem    #修改表 这个表是子表
add foreign key(orderid)  #添加外键
references orders(orderid)  #参照完整性约束  references后的表就是父表
on delete restrict   
#当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。
on update cascade;
#从父表删除或更新行时自动删除或更新子表中匹配的行。
show create table lineitem; #查看表信息
#####Begin########
#####End##########

####相关知识

为了完成本关任务，你需要掌握：参照完整性约束

语法

1.参照完整性约束通过外键实现。可以在创建表或修改表时定义一个外键声明。

定义外键的reference_definition语法格式如下：

REFERENCES 表名 [(列名 [(长度)] [ASC | DESC],...)]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
RESTRICT：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。
CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行。
SET NULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。
NO ACTION：NO ACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样。
SET DEFAULT：作用和SET NULL一样，只不过SET DEFAULT是指定子表中的外键列为默认值。
如果没有指定动作，两个参照动作就会默认地使用RESTRICT。

2.使用："show create table 表名;"查看创建表的信息
 

第4关：Check完整性约束

任务描述

本关任务：
1.为用户account表的性别sex列添加check约束，保证性别的值只能是男或女。
2.为订单详情lineitem表的订单号orderid列添加check约束，保证订单详情表中的订单号orderid列的值都来自订单orders表。
3.查看创建lineitem表的信息。

USE petstore;    #跳转数据库
alter table account   #修改表
add check(sex in('男','女'));  #check完整性约束,保证性别的值只能是男或女
alter table lineitem
add check(orderid in(select orderidvfromvorders));
#保证订单详情表中的订单号orderid列的值都来自订单orders表
show create table lineitem;   #查看创建表的信息
#####Begin########
#####End##########

####相关知识

为了完成本关任务，你需要掌握：CHECK完整性约束

语法

1.CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约束。
语法格式为：
CHECK(expr)
说明：expr是一个表达式，指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足CHECK的条件。
2.使用:"alter table 表名 add check(...);"为表中的列添加check约束。
3.使用："show create table 表名;"查看创建表的信息。