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

一、定义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" 
WHERE
	sno = "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引用的主码值S1
 
DELETE 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;