实验三 数据库完整性 (头歌)

已于 2024-04-08 18:38:46 修改
阅读量1.5k 收藏 13
点赞数 13
分类专栏： 笔记 文章标签： 数据库 sql oracle
于 2024-04-05 16:33:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_52754254/article/details/137403107
版权

实验三 数据库完整性 (头歌)

制作不易！点个关注！给大家创造更多的价值！

目录

第一关：定义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，表结构如下表所示。

字段名称数据类型备注
idINT(11)部门编号
nameVARCHAR(22)部门名称
locationVARCHAR(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表中插入违反约束的数据，外码取对应主码没有的值

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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供应)

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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供应)

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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）

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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）

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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。

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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

原始数据如下：

snosnamestatuscity
S1精益20天津
S2盛锡10北京
S3东方红30北京
S4丰泰盛20天津
S5为民30上海
pnopnamecolorweight
p1螺母红12
p2螺栓绿17
p3螺丝刀蓝14
p4螺丝刀红14
p5凸轮蓝40
p6凸轮红30
jnojpnamecity
j1三建北京
j2一汽长春
j3弹簧厂天津
j4造船厂天津
j5机车厂唐山
j6无线电厂常州
j7半导体厂南京
snopnojnoqty
S1P1J1200
S1P1J3100
S1P1J4700
S1P2J2100
S2P3J1400
S2P3J2200
S2P3J4500
S2P3J5400
S2P5J1400
S2P5J2100
S3P1J1200
S3P3J1200
S4P5J1100
S4P6J3300
S4P6J4200
S5P2J4100
S5P3J1200
S5P6J2200
S5P6J4500

测试说明

平台会对你编写的代码进行测试：

预期输出：

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;

制作不易！点个关注！给大家创造更多的价值！

霸敛
关注
  • 13
    点赞
  • 踩
  • 13
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
实验3 数据库安全性与完整性控制实验.doc
07-07
实验3 数据库安全性与完整性控制是国家开放大学数据运维课程的一部分，主要目的是让学生熟悉MySQL数据库的安全管理和完整性控制机制。实验涵盖了用户创建、权限授予、权限查看以及表结构设计和完整性约束的应用。 ...
实验三 数据库的安全性和完整性控制.doc
05-21
实验三 数据库的安全性和完整性控制 （1）通过本实验能够熟练应用sql语言进行用户权限的授予和回收。 （2） 熟练掌握实体完整性，参照完整性及用户定义的完整性的定义。 （3）体会oracle数据库系统在安全性和完整性...
头歌-参照完整性
m0_62931214的博客
09-18 502
本关任务：定义选修课表的参照完整性。
Java数据结构-线性表的设计与实现(头歌平台，详细注释)
qwertf123的博客
09-18 1506
Java数据结构-线性表的设计与实现
头歌 第1关：用户定义的完整性
weixin_63839726的博客
09-25 1324
任何关系数据库系统都应该支持实体完整性和参照完整性，这是关系模型所要求的。除此之外，不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。力图某个属性必须取唯一值、某个非主属性不能取空值等。在 CREATE TABLE 中定义属性的同时，可以根据应用要求定义属性上的约束条件，即属性值限制，包括：列值非空（NOT NULL）列值唯一（UNIQUE）
头歌--第2关：建立读者数据表
Zjyzzy123456789的博客
03-21 4975
在字段类型选择的时候， 对于数值类型，ZEROFILL 表示前导零填充数值类型值以达到列的显示宽度。创建数据表的命令格式： CREATE TABLE 表名 　　( 列名1　　　数据类型 字段属性, 列名2　　　 数据类型 字段属性, …在建立数据表的时候，。在建立数据表的时候，字段名 数据类型 PRIMARY KEY。本关任务：建立数据表 为了完成本关任务，你需要掌握： 如何选择数据库 如何创建数据表 设置数据表的原则 如何显示数据表的结构。命令格式： use 数据库名 选择数据库为当前数据库。
数据库系统头歌实验八 数据库完整性、安全设计
m0_53227534的博客
12-23 4139
SPJ_SUMQTY表为工程零件数量统计表，由工程项目代码(JNO)、零件代码(PNO)、统计供应数量SUMQTY组成，其中SUMQTY由SPJ表中的工程项目代码(JNO)、零件代码(PNO)、供应数量(QTY)根据JNO、PNO汇总得到。SPJ_SUMQTY表为工程零件数量统计表，由工程项目代码(JNO)、零件代码(PNO)、统计供应数量SUMQTY组成，其中SUMQTY由SPJ表中的工程项目代码(JNO)、零件代码(PNO)、供应数量(QTY)根据JNO、PNO汇总得到。
数据库完整性实验报告.doc
11-23
数据库完整性实验报告 数据库完整性是数据库管理系统中的一种机制，用于确保数据库中的数据正确性、完整性和一致性。在本实验报告中，我们将学习实体完整性、参照完整性和自定义完整性，以及触发器的概念和应用。 ...
数据库实验5-数据库完整性.docx
12-19
数据库实验5-数据库完整性 数据库实验5-数据库完整性是数据库管理系统中的一项重要实验，旨在熟悉数据库的交互式SQL工具，通过SQL对数据库进行操作，完成作业的上机练习。本实验报告主要涉及到数据库的创建、基本表...
数据库实验3报告（数据完整性与安全性控制）.docx
06-28
【数据库实验3报告——数据完整性与安全性控制】 本次实验主要涉及了数据库中关于数据完整性和安全性的关键概念和操作，具体包括数据约束的设定、表的创建与删除、权限的管理等多个方面。以下是对实验内容的详细...
头哥实践平台操作系统实验-实训三 Linux 共享内存1
07-05
头哥操作系统实验学习参考代码
头歌:信息安全技术之完整性和安全性
wangmyang0909的博客
12-18 1568
头歌实验:信息安全技术之完整性和安全性,第1关：完整性约束的SQL定义,第2关：数据库安全
数据库实验报告八（头歌）
Coisioi的博客
11-29 1431
文章仅供学习参考
实验4 数据库安全性和完整性
m0_51877411的博客
05-03 2379
对已建好的各表和用户进行权限的授权和回收操作，进行完操作之后检查授权过的用户是否真正具有授予的数据操作的权力了，进行完权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。 定义各种完整性约束条件，之后输入各种数据以验证约束条件的作用。 设置用户a对SPJ表的查询权限。以a登录，验证a的权限。 SELECT * FROM mysql.user;查看用户名和主机地址 查看当前用户权限：show grants for 用户名@'主机地址'； create role a gran...
头歌(数据库实验三)数据查询
weixin_71582779的博客
05-15 3264
select qxmc,count(*) from jdxx where cs="长沙市" group by qxmc;select count(distinct qxmc) from jdxx where cs="长沙市";select * from jdxx where cs="长沙市" and name="西湖街道";select * from jdxx where qxmc="开福区" or qxmc="岳麓区";第1关：按条件查询单表的所有字段。
数据库系统头歌实验三 SQL的复杂多表查询以及视图1
m0_53227534的博客
12-23 3583
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。
数据库头歌实训（MYSQL）：头歌数据库symql实训作业相关知识点
qq_63949327的博客
11-23 3091
根据头歌数据库symql实训作业相关知识点
【头歌系统数据库实验】实验14 数据库编程-2
最新发布
学习的博客
01-02 1776
任务描述使用游标定义一个名为PROC_JGRADE无参存储过程，统计各工程使用零件数量的分布情况，按照S档2000分段统计，即输出工程JNO、每个工程使用零件个数、每个工程使用零件个数的分档，结果按工程号JNO升序排序。相关知识1、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。SPJ表如下图：现已构建SPJ表，结构信息如下：2、游标。
完整性约束 第一关
qq_62449917的博客
04-20 2534
第1关：主键约束 任务描述 本关任务： 1.创建一个tmp表，并查看tmp表结构，tmp表结构如下： 2.创建一个stock（库存)表，并查看表结构，stock表结构如下： ####相关知识 为了完成本关任务，你需要掌握：定义主键 语法 1.主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARY KEY约束来创建主键，而且PRIMARY KEY约束中的列不能取空值。由于PRIMARY KEY约束能确保数据的唯一，所以经常用来定义标志列。当为表定义..
数据库实验：SQL操作与数据库完整性
"该资源是关于数据库实验的文档，主要探讨了数据库完整性的概念和实践。实验使用MSSQLServer2012作为工具，涵盖了数据库的创建、表的定义、数据插入以及完整性约束的应用。实验内容包括创建MT+学号的数据库，建立...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值