实验三 数据库完整性 (头歌)
制作不易!点个关注!给大家创造更多的价值!
目录
- 实验三 数据库完整性 (头歌)
- `制作不易!点个关注!给大家创造更多的价值!`
- 第一关:定义s表完整性
- 第二关:定义p表完整性
- 第3关:定义j表完整性
- 第4关:定义spj表完整性
- 第5关:实体完整性测试(1)
- 第6关:实体完整性测试(2)
- 第7关:用户自定义完整性测试(1)
- 第8关:用户自定义完整性测试(2)
- 第9关:参考完整性测试(1)
- 第10关:参照完整性测试(2)
- 第11关:参照完整性测试(3)
- 第12关:参照完整性测试(4)
- 第13关:参照完整性测试(5)
- 第14关:参照完整性测试(6)
- 第15关:参照完整性测试(7)
- 第16关:参照完整性测试(8)
- 第17关:级联删除、级联修改
- 第18关:级联删除、级联修改测试(1)
- 第19关:级联删除、级联修改测试(2)
- `制作不易!点个关注!给大家创造更多的价值!`
第一关:定义s表完整性
相关知识
MySQL约束概述
在 MySQL 中,约束是指对表中数据的一种约束,能够帮助数据库管理员更好地管理数据库,并且能够确保数据库中数据的正确性和有效性。
例如,在数据表中存放年龄的值时,如果存入 200、300 这些无效的值就毫无意义了。因此,使用约束来限定表中的数据范围是很有必要的。
主键约束
主键约束是使用最频繁的约束。在设计数据表时,一般情况下,都会要求表中设置一个主键。
主键是表的一个特殊字段,该字段能唯一标识该表中的每条信息。例如,学生信息表中的学号是唯一的。
1)设置单字段主键
在 CREATE TABLE 语句中,通过 PRIMARY KEY 关键字来指定主键。
在定义字段的同时指定主键,语法格式如下:
<字段名> <数据类型> PRIMARY KEY [默认值]
例 1
在 test_db 数据库中创建 tb_emp3 数据表,其主键为 id,SQL 语句和运行结果如下。
mysql> CREATE TABLE tb_emp3
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
或者是在定义完所有字段之后指定主键,语法格式如下:
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
例 2
在 test_db 数据库中创建 tb_emp4 数据表,其主键为 id,SQL 语句和运行结果如下。
mysql> CREATE TABLE tb_emp4
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp4;
2)在创建表时设置联合主键
所谓的联合主键,就是这个主键是由一张表中多个字段组成的。
比如,设置学生选课数据表时,使用学生编号做主键还是用课程编号做主键呢?如果用学生编号做主键,那么一个学生就只能选择一门课程。如果用课程编号做主键,那么一门课程只能有一个学生来选。显然,这两种情况都是不符合实际情况的。
实际上设计学生选课表,要限定的是一个学生只能选择同一课程一次。因此,学生编号和课程编号可以放在一起共同作为主键,这也就是联合主键了。
主键由多个字段联合组成,语法格式如下:
PRIMARY KEY [字段1,字段2,…,字段n]
注意:当主键是由多个字段组成时,不能直接在字段名后面声明主键约束。
例 3
创建数据表 tb_emp5,假设表中没有主键 id,为了唯一确定一个员工,可以把 name、deptId 联合起来作为主键,SQL 语句和运行结果如下。
mysql> CREATE TABLE tb_emp5
-> (
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> PRIMARY KEY(name,deptId)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp5;
非空约束
非空约束用来约束表中的字段不能为空。例如,在学生信息表中,如果不添加学生姓名,那么这条记录是没有用的。
创建表时可以使用 NOT NULL 关键字设置非空约束,具体的语法格式如下:
<字段名> <数据类型> NOT NULL;
例如:创建数据表 tb_dept4,指定部门名称不能为空,SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_dept4
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22) NOT NULL,
-> location VARCHAR(50)
-> );
Query OK, 0 rows affected (0.37 sec)
默认值约束
默认值约束用来约束当数据表中某个字段不输入值时,自动为其添加一个已经设置好的值。
例如,在注册学生信息时,如果不输入学生的性别,那么会默认设置一个性别或者输入一个“未知”。
默认值约束通常用在已经设置了非空约束的列,这样能够防止数据表在录入数据时出现错误。
创建表时可以使用 DEFAULT 关键字设置默认值约束,具体的语法格式如下:
<字段名> <数据类型> DEFAULT <默认值>;
其中,“默认值”为该字段设置的默认值,如果是字符类型的,要用单引号括起来。
例如:创建数据表 tb_dept3,指定部门位置默认为 Beijing,SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_dept3
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22),
-> location VARCHAR(50) DEFAULT 'Beijing'
-> );
Query OK, 0 rows affected (0.37 sec)
查看表中的约束
在 MySQL 中可以使用 SHOW CREATE TABLE 语句来查看表中的约束。
查看数据表中的约束语法格式如下:
SHOW CREATE TABLE <数据表名>;
例如:创建数据表 tb_emp8 并指定 id 为主键约束,name 为唯一约束,deptId 为非空约束和外键约束,然后查看表中的约束,SQL 语句运行结果如下。
mysql> CREATE TABLE tb_emp8
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22) UNIQUE,
-> deptId INT(11) NOT NULL,
-> salary FLOAT DEFAULT 0,
-> CHECK(salary>0),
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> SHOW CREATE TABLE tb_emp8 \G
*************************** 1. row ***************************
Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`deptId` int(11) NOT NULL,
`salary` float DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `deptId` (`deptId`),
CONSTRAINT `tb_emp8_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.19 sec)
编程要求
请按下面s表的结构定义完整性; sno主码,sname非空、city缺省值为天津。
create table s(
sno char(2),
sname varchar(10),
status int,
city varchar(10)
);
create table s(
sno char(2) PRIMARY KEY,
sname varchar(10) not null,
status int,
city varchar(10) DEFAULT '天津'
);
第二关:定义p表完整性
相关知识
MySQL检查约束(CHECK)
MySQL 检查约束(CHECK)是用来检查数据表中字段值有效性的一种手段,可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置,这样能够减少无效数据的输入。
创建表时设置检查约束
语法格式如下:
CHECK(<检查约束>)
例如:在 test_db 数据库中创建 tb_emp7 数据表,要求 salary 字段值大于 0 且小于 10000,SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_emp7
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CHECK(salary>0 AND salary<100),
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)
在修改表时添加检查约束
如果一个表创建完成,可以通过修改表的方式为表添加检查约束。
修改表时设置检查约束的语法格式如下:
ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
例如:修改 tb_emp7 数据表,要求 id 字段值大于 0,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp7
-> ADD CONSTRAINT check_id
-> CHECK(id>0);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除检查约束
修改表时删除检查约束的语法格式如下:
ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
例如:删除 tb_emp7 表中的 check_id 检查约束,SQL 语句和运行结果如下所示:
mysql> ALTER TABLE tb_emp7
-> DROP CONSTRAINT check_id;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
编程要求
请按下面p表的结构定义完整性; pno主码,pname非空、color只能取红、蓝、绿。
create table p(
pno char(2),
pname varchar(10),
color char(1),
weight int
);
代码如下:
use demo;
#代码开始
#定义p表; pno主码,pname非空、color只能取红、蓝、绿
create table p(
pno char(2) PRIMARY KEY,
pname varchar(10) not null,
color char(1),
CHECK(((color = _utf8mb3'红')or(color = _utf8mb3'蓝')or(color = _utf8mb3'绿'))),
weight int
);
#代码结束
show create table p;
第3关:定义j表完整性
编程要求
请按下面j表的结构定义完整性; jno主码, jname非空。
create table j(
jno char(2),
jname varchar(10),
city varchar(10)
);
代码如下:
use demo;
#代码开始
#定义j表; jno主码, jname非空
create table j(
jno char(2) primary key,
jname varchar(10) not null,
city varchar(10)
);
#代码结束
show create table j;
第4关:定义spj表完整性
相关知识
MySQL外键约束(FOREIGN KEY)
MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。
外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。比如,一个水果摊,只有苹果、桃子、李子、西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜,其它的水果都是不能购买的。
主表删除某条记录时,从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键,外键可以为空值,若不为空值,则每一个外键的值必须等于主表中主键的某个值。
在创建表时设置外键约束
在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键,具体的语法格式如下:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]
例如:为了展现表与表之间的外键关系,本例在 test_db 数据库中创建一个部门表 tb_dept1,表结构如下表所示。
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT(11) | 部门编号 |
name | VARCHAR(22) | 部门名称 |
location | VARCHAR(22) | 部门位置 |
创建 tb_dept1 的 SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_dept1
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(22) NOT NULL,
-> location VARCHAR(50)
-> );
Query OK, 0 rows affected (0.37 sec)
创建数据表 tb_emp6,并在表 tb_emp6 上创建外键约束,让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id,SQL 语句和运行结果如下所示。
mysql> CREATE TABLE tb_emp6
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT,
-> CONSTRAINT fk_emp_dept1
-> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
-> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| deptId | int(11) | YES | MUL | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)
以上语句执行成功之后,在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束,外键名称为 deptId,其依赖于表 tb_dept1 的主键 id。
注意:从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。例如,两者都是 INT 类型,或者都是 CHAR 类型。如果不满足这样的要求,在创建从表时,就会出现“ERROR 1005(HY000): Can’t create table”错误。
编程要求
请按下面spj表的结构定义完整性; (sno,pno,jno)主码,参照sno、pno、jno外码,外键约束名称依次为fk_spj_sno、fk_spj_pno、fk_spj_jno。
create table spj(
sno char(2),
pno char(2),
jno char(2),
qty int
);
代码如下:
use demo;
#代码开始
#定义spj表; (sno,pno,jno)主码,参照sno、pno、jno外码
#外键约束名称依次为fk_spj_sno、fk_spj_pno和fk_spj_jno。
create table spj(
sno char(2) not null,
pno char(2) not null,
jno char(2) not null,
qty int default null,
primary key(sno,pno,jno),
key fk_spj_pno (pno),
key fk_spj_jno (jno),
CONSTRAINT fk_spj_jno
FOREIGN KEY (jno) REFERENCES j (jno),
CONSTRAINT fk_spj_pno
FOREIGN KEY (pno) REFERENCES p (pno),
CONSTRAINT fk_spj_sno
FOREIGN KEY (sno) REFERENCES s (sno)
);
#代码结束
show create table spj;
第5关:实体完整性测试(1)
编程要求
设计相关实验用例数据,观察数据运行结果。
(1)在s表中插入违反约束的数据,主码为null值
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1048 (23000) at line 7: Column 'sno' cannot be null
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(1)在s表中插入违反约束的数据,主码为null值
insert into s values(null,null,null,null);
#代码结束
第6关:实体完整性测试(2)
编程要求
设计相关实验用例数据,观察数据运行结果。
(2)在s表中插入违反约束的数据,主码取重复值
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1062 (23000) at line 6: Duplicate entry 'S1' for key 's.PRIMARY'
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(2)在s表中插入违反约束的数据,主码取重复值
insert into s values('S1','S1',null,null);
#代码结束
第7关:用户自定义完整性测试(1)
编程要求
设计相关实验用例数据,观察数据运行结果。
(1)在p表中插入违反约束的数据,用户定义完整性(pname非空)
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1048 (23000) at line 6: Column 'pname' cannot be null
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(1)在p表中插入违反约束的数据,用户定义完整性(pname非空)
insert into p values("P1",null,null,null);
#代码结束
第8关:用户自定义完整性测试(2)
编程要求
设计相关实验用例数据,观察数据运行结果。
(2)在p表中插入违反约束的数据,用户定义完整性(color只能取红、蓝、绿)
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 3819 (HY000) at line 6: Check constraint 'p_chk_1' is violated.
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(2)在p表中插入违反约束的数据,用户定义完整性(color只能取红、蓝、绿)
insert into p values('p2',"p2","黄",null)
#代码结束
第9关:参考完整性测试(1)
编程要求
设计相关实验用例数据,观察数据运行结果。
(1)在spj表中插入违反约束的数据,外码取null值
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1048 (23000) at line 7: Column 'sno' cannot be null
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(1)在spj表中插入违反约束的数据,外码取null值
insert into spj values("p1","p1",null,null);
#代码结束
第10关:参照完整性测试(2)
编程要求
设计相关实验用例数据,观察数据运行结果。
(2)在spj表中插入违反约束的数据,外码取对应主码没有的值
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1452 (23000) at line 6: Cannot add or update a child row: a foreign key constraint fails (`demo`.`spj`, CONSTRAINT `fk_spj_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`))
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(2)在spj表中插入违反约束的数据,外码取对应主码没有的值
insert into spj values('11','11','11',100);
#代码结束
第11关:参照完整性测试(3)
编程要求
设计相关实验用例数据,观察数据运行结果。
(3)在spj表中修改外码值,取对应主码已有的值(将S1,P1,J1的记录改为由S5供应)
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
sno pno jno qty
S5 P1 J1 200
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(3)在spj表中修改外码值,取对应主码已有的值(将S1,P1,J1的记录改为由S5供应)
update spj set sno="S5" where sno="S1" and pno="P1" and jno="J1";
#代码结束
select * from spj where sno='S5' and pno='P1' and jno='J1';
第12关:参照完整性测试(4)
编程要求
设计相关实验用例数据,观察数据运行结果。
(4)在spj表中修改外码值,取对应主码没有的值(将S1,P1,J1的记录改为由S7供应)
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1452 (23000) at line 6: Cannot add or update a child row: a foreign key constraint fails (`demo`.`spj`, CONSTRAINT `fk_spj_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`))
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(4)在spj表中修改外码值,取对应主码没有的值(将S1,P1,J1的记录改为由S7供应)
update spj set sno="S7" where sno="S1" and pno="P1" and jno="J1"
#代码结束
第13关:参照完整性测试(5)
编程要求
设计相关实验用例数据,观察数据运行结果。
(5)删除被参照表J未引用的主码值J7
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
jno jname city
J1 三建 北京
J2 一汽 长春
J3 弹簧厂 天津
J4 造船厂 天津
J5 机车厂 唐山
J6 无线电厂 常州
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(5)删除被参照表J未引用的主码值J7
delete from j where jno="J7";
#代码结束
select * from j;
第14关:参照完整性测试(6)
编程要求
设计相关实验用例数据,观察数据运行结果。
(6)删除被参照表S引用的主码值S1
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1451 (23000) at line 6: Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`spj`, CONSTRAINT `fk_spj_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`))
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(6)删除被参照表S引用的主码值S1
-- delete from spj where spj.sno="S1";
delete from s where s.sno="S1";
-- select * from spj;
#代码结束
第15关:参照完整性测试(7)
编程要求
设计相关实验用例数据,观察数据运行结果。
(7)修改被参照表未引用的主码值(将J表J6改为J8)
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
jno jname city
J1 三建 北京
J2 一汽 长春
J3 弹簧厂 天津
J4 造船厂 天津
J5 机车厂 唐山
J7 半导体厂 南京
J8 无线电厂 常州
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(7)修改被参照表未引用的主码值(将J表J6改为J8)
update j set jno="J8" where jno="J6";
#代码结束
select * from j;
第16关:参照完整性测试(8)
编程要求
设计相关实验用例数据,观察数据运行结果。
(8)修改被参照表引用的主码值(将S表S5改为S7)
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
ERROR 1451 (23000) at line 6: Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`spj`, CONSTRAINT `fk_spj_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`))
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(8)修改被参照表引用的主码值(将S表S5改为S7)
update s set sno="S7" where sno="S5";
#代码结束
第17关:级联删除、级联修改
相关知识
删除外键约束
当一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系。
删除外键约束的语法格式如下所示:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
例如:删除数据表 tb_emp2 中的外键约束 fk_tb_dept1,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp2
-> DROP FOREIGN KEY fk_tb_dept1;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
`id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)
可以看到,tb_emp2 中已经不存在 FOREIGN KEY,原有的名称为 fk_emp_dept 的外键约束删除成功。
级联更新与级联删除
添加级联更新和级联删除时需要在外键约束后面添加
在删除父表中的数据时,级联删除子表中的数据 on delete cascade
在更新父表中的数据时,级联更新子表中的数据 on update cascade
以上的级联更新和级联删除谨慎使用,因为级联操作会将数据改变或删除【数据无价】。在修改约束条件时,建议可以将原先的约束删除再重新添加约束条件。
例如:
删除外键约束:
ALTER TABLE student DROP FOREIGN KEY fk_student_sno;
添加级联删除的外键约束:
ALTER TABLE student ADD CONSTRAINT fk_student_sno FOREIGN KEY(sno) REFERENCES class(cno) ON DELETE CASCADE;
添加级联更新的外键约束:
ALTER TABLE student ADD CONSTRAINT fk_student_sno FOREIGN KEY(sno) REFERENCES class(cno) ON UPDATE CASCADE;
也可以同时添加级联删除和级联更新的外键约束:
ALTER TABLE student ADD CONSTRAINT fk_student_sno FOREIGN KEY(sno) REFERENCES class(cno) ON DELETE CASCADE ON UPDATE CASCADE;
编程要求
设计相关实验用例数据,观察数据运行结果。
三、将外码改成级联删除、级联修改
提示:先删除外键约束,再添加级联删除和级联更新。
原表定义外键约束名称依次为fk_spj_sno、fk_spj_pno、fk_spj_jno。
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
Table Create Table
spj CREATE TABLE `spj` (\n `sno` char(2) NOT NULL,\n `pno` char(2) NOT NULL,\n `jno` char(2) NOT NULL,\n `qty` int DEFAULT NULL,\n PRIMARY KEY (`sno`,`pno`,`jno`),\n KEY `fk_spj_pno` (`pno`),\n KEY `fk_spj_jno` (`jno`),\n CONSTRAINT `fk_spj_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `fk_spj_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `fk_spj_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
代码如下:
use demo;
#代码开始
#三、将外码改成级联删除、级联修改(原表定义外键约束名称依次为`fk_spj_sno`、`fk_spj_pno`、`fk_spj_jno`)。
#1、删除外键约束
ALTER TABLE spj DROP FOREIGN KEY fk_spj_sno;
ALTER TABLE spj DROP FOREIGN KEY fk_spj_pno;
ALTER TABLE spj DROP FOREIGN KEY fk_spj_jno;
#2、修改表spj添加级联删除和级联更新
ALTER TABLE spj ADD CONSTRAINT fk_spj_pno FOREIGN KEY(pno) REFERENCES p (pno) ON DELETE CASCADE ON update CASCADE;
ALTER TABLE spj ADD CONSTRAINT fk_spj_jno FOREIGN KEY(jno) REFERENCES j (jno) ON DELETE CASCADE ON update CASCADE;
ALTER TABLE spj ADD CONSTRAINT fk_spj_sno FOREIGN KEY(sno) REFERENCES s (sno) ON DELETE CASCADE ON update CASCADE;
#代码结束
show create table spj;
第18关:级联删除、级联修改测试(1)
编程要求
设计相关实验用例数据,观察数据运行结果。
(1)删除被参照表S引用的主码值S1
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
sno sname status city
S2 盛锡 10 北京
S3 东方红 30 北京
S4 丰泰盛 20 天津
S5 为民 30 上海
sno pno jno qty
S2 P3 J1 400
S2 P3 J2 200
S2 P3 J4 500
S2 P3 J5 400
S2 P5 J1 400
S2 P5 J2 100
S3 P1 J1 200
S3 P3 J1 200
S4 P5 J1 100
S4 P6 J3 300
S4 P6 J4 200
S5 P2 J4 100
S5 P3 J1 200
S5 P6 J2 200
S5 P6 J4 500
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(1)删除被参照表S引用的主码值S1
delete from s where s.sno="S1";
#代码结束
select * from s;
select * from spj;
第19关:级联删除、级联修改测试(2)
编程要求
设计相关实验用例数据,观察数据运行结果。
(2)修改被参照表S,将引用的主码值S5改为S7
原始数据如下:
sno | sname | status | city |
---|---|---|---|
S1 | 精益 | 20 | 天津 |
S2 | 盛锡 | 10 | 北京 |
S3 | 东方红 | 30 | 北京 |
S4 | 丰泰盛 | 20 | 天津 |
S5 | 为民 | 30 | 上海 |
pno | pname | color | weight |
---|---|---|---|
p1 | 螺母 | 红 | 12 |
p2 | 螺栓 | 绿 | 17 |
p3 | 螺丝刀 | 蓝 | 14 |
p4 | 螺丝刀 | 红 | 14 |
p5 | 凸轮 | 蓝 | 40 |
p6 | 凸轮 | 红 | 30 |
jno | jpname | city |
---|---|---|
j1 | 三建 | 北京 |
j2 | 一汽 | 长春 |
j3 | 弹簧厂 | 天津 |
j4 | 造船厂 | 天津 |
j5 | 机车厂 | 唐山 |
j6 | 无线电厂 | 常州 |
j7 | 半导体厂 | 南京 |
sno | pno | jno | qty |
---|---|---|---|
S1 | P1 | J1 | 200 |
S1 | P1 | J3 | 100 |
S1 | P1 | J4 | 700 |
S1 | P2 | J2 | 100 |
S2 | P3 | J1 | 400 |
S2 | P3 | J2 | 200 |
S2 | P3 | J4 | 500 |
S2 | P3 | J5 | 400 |
S2 | P5 | J1 | 400 |
S2 | P5 | J2 | 100 |
S3 | P1 | J1 | 200 |
S3 | P3 | J1 | 200 |
S4 | P5 | J1 | 100 |
S4 | P6 | J3 | 300 |
S4 | P6 | J4 | 200 |
S5 | P2 | J4 | 100 |
S5 | P3 | J1 | 200 |
S5 | P6 | J2 | 200 |
S5 | P6 | J4 | 500 |
测试说明
平台会对你编写的代码进行测试:
预期输出:
sno sname status city
S1 精益 20 天津
S2 盛锡 10 北京
S3 东方红 30 北京
S4 丰泰盛 20 天津
S7 为民 30 上海
sno pno jno qty
S1 P1 J1 200
S1 P1 J3 100
S1 P1 J4 700
S1 P2 J2 100
S2 P3 J1 400
S2 P3 J2 200
S2 P3 J4 500
S2 P3 J5 400
S2 P5 J1 400
S2 P5 J2 100
S3 P1 J1 200
S3 P3 J1 200
S4 P5 J1 100
S4 P6 J3 300
S4 P6 J4 200
S7 P2 J4 100
S7 P3 J1 200
S7 P6 J2 200
S7 P6 J4 500
代码如下:
use demo;
#代码开始
#设计相关实验用例数据,观察数据运行结果。
#(2)修改被参照表S,将引用的主码值S5改为S7
update s set s.sno="S7" where s.sno="S5";
#代码结束
select * from s;
select * from spj;