数据库：头歌实验三数据库完整性

一、定义s表完整性

编程要求

请按下面s表的结构定义完整性； sno主码，sname非空、city缺省值为天津。

create table s(
sno char(2),
sname varchar(10),
status int,
city varchar(10)
);

use demo;#代码开始#定义s表； sno主码，sname非空、city缺省值为天津。
CREATE TABLE s(sno CHAR(2) PRIMARY KEY,sname VARCHAR(10) NOT NULL,status int,city VARCHAR(10) DEFAULT'天津'
);#代码结束show create table s;

二、定义p表完整性

编程要求

请按下面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 = '红')or(color = '蓝')or(color = '绿'))),weight int
);#代码结束show create table p;

三、定义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;

四、定义spj表完整性

编程要求

请按下面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),pno CHAR(2),jno CHAR(2),qty int,PRIMARY KEY(sno,pno,jno),CONSTRAINT fk_spj_sno FOREIGN KEY(sno) REFERENCES s(sno),CONSTRAINT fk_spj_pno FOREIGN KEY(pno) REFERENCES p(pno),CONSTRAINT fk_spj_jno FOREIGN KEY(jno) REFERENCES j(jno)
);#代码结束show create table spj;

五、实体完整性测试（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 );#代码结束

六、实体完整性测试（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 );#代码结束

七、用户自定义完整性测试（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 );
#代码结束

八、用户自定义完整性测试（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 );
#代码结束

九、参考完整性测试（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 );#代码结束

十、参照完整性测试（2）

编程要求

设计相关实验用例数据，观察数据运行结果。
（2）在spj表中插入违反约束的数据，外码取对应主码没有的值

原始数据如下：

测试说明

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

预期输出：

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( 'ok', 'ok', 'ok', 100 );#代码结束

十一、参照完整性测试（3）

编程要求

设计相关实验用例数据，观察数据运行结果。
（3）在spj表中修改外码值，取对应主码已有的值(将S1,P1,J1的记录改为由S5供应)

测试说明

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

预期输出：

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';

十二、参照完整性测试（4）

编程要求

设计相关实验用例数据，观察数据运行结果。
（4）在spj表中修改外码值，取对应主码没有的值(将S1,P1,J1的记录改为由S7供应)

测试说明

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

预期输出：

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" 
WHEREsno = "S1" AND pno = "P1" AND jno = "J1";#代码结束

十三、参照完整性测试（5）

编程要求

设计相关实验用例数据，观察数据运行结果。
（5）删除被参照表J未引用的主码值J7

测试说明

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

预期输出：

jno jname city
J1 三建北京
J2 一汽长春
J3 弹簧厂天津
J4 造船厂天津
J5 机车厂唐山
J6 无线电厂常州

 use demo;#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（5）删除被参照表J未引用的主码值J7
DELETE FROM j WHERE jno = "J7";#代码结束select * from j;

十四、参照完整性测试（6）

编程要求

设计相关实验用例数据，观察数据运行结果。
（6）删除被参照表S引用的主码值S1

测试说明

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

预期输出：

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	s WHERE	s.sno = "S1";#代码结束

十五、参照完整性测试（7）

编程要求

设计相关实验用例数据，观察数据运行结果。
（7）修改被参照表未引用的主码值（将J表J6改为J8）

测试说明

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

预期输出：

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;

十六、参照完整性测试（8）

编程要求

设计相关实验用例数据，观察数据运行结果。
（8）修改被参照表引用的主码值（将S表S5改为S7）

测试说明

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

预期输出：

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";#代码结束

十七、级联删除、级联修改

编程要求

设计相关实验用例数据，观察数据运行结果。
三、将外码改成级联删除、级联修改
提示：先删除外键约束，再添加级联删除和级联更新。
原表定义外键约束名称依次为fk_spj_sno、fk_spj_pno、fk_spj_jno。

测试说明

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

预期输出：

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添加级联删除和级联更新（外键约束名称依次为`fk_spj_sno`、`fk_spj_pno`、`fk_spj_jno`）
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;

十八、级联删除、级联修改测试（1）

编程要求

设计相关实验用例数据，观察数据运行结果。
（1）删除被参照表S引用的主码值S1

测试说明

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

use demo;#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（1）删除被参照表S引用的主码值S1DELETE FROM	s WHERE	s.sno = "S1";#代码结束
select * from s;
select * from spj;

十九、级联删除、级联修改测试（2）

编程要求

设计相关实验用例数据，观察数据运行结果。
（2）修改被参照表S，将引用的主码值S5改为S7

测试说明

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

use demo;#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（2）修改被参照表S，将引用的主码值S5改为S7
UPDATE s SET s.sno = "S7" WHERE	s.sno = "S5";#代码结束select * from s;
select * from spj;