实验三 数据库完整性

第1关：定义s表完整性

create table s(
  sno char(2)  PRIMARY KEY,
  sname varchar(10) not null,
  status int,
  city varchar(10) DEFAULT '天津'
);

第2关：定义p表完整性

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表完整性

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表完整性

编程要求

请按下面spj表的结构定义完整性； (sno,pno,jno)主码，参照sno、pno、jno外码，外键约束名称依次为fk_spj_sno、fk_spj_pno、fk_spj_jno。

1. create table spj(
2. sno char(2),
3. pno char(2),
4. jno char(2),
5. qty int
6. );

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值

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（1）在s表中插入违反约束的数据，主码为null值
insert into s values(null,null,null,null);

#代码结束

第6关：实体完整性测试（2）

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（2）在s表中插入违反约束的数据，主码取重复值
insert into s values('S1','S1',null,null);

#代码结束

第7关：用户自定义完整性测试（1）

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（1）在p表中插入违反约束的数据，用户定义完整性（pname非空）
insert into p values("P1",null,null,null);

#代码结束

第8关：用户自定义完整性测试（2）

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（2）在p表中插入违反约束的数据，用户定义完整性（color只能取红、蓝、绿）
insert into p values('p2',"p2","黄",null)

#代码结束

第9关：参考完整性测试（1）

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（1）在spj表中插入违反约束的数据，外码取null值
insert into spj values("p1","p1",null,null);

#代码结束

第10关：参照完整性测试（2）

use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（2）在spj表中插入违反约束的数据，外码取对应主码没有的值
insert into spj values('11','11','11',100);

#代码结束

第11关：参照完整性测试（3）

 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）

 use demo;

#代码开始

#设计相关实验用例数据，观察数据运行结果。

#（4）在spj表中修改外码值，取对应主码没有的值(将S1,P1,J1的记录改为由S7供应)

update spj set sno="S7" where sno="S1" and pno="P1" and jno="J1"

#代码结束

第13关：参照完整性测试（5）

 use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。

#（5）删除被参照表J未引用的主码值J7
delete from j where jno="J7";

#代码结束

select * from j;

第14关：参照完整性测试（6）

 use demo;

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

第15关：参照完整性测试（7）

 use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（7）修改被参照表未引用的主码值（将J表J6改为J8）
update j set jno="J8" where jno="J6";

#代码结束

select * from j;

第16关：参照完整性测试（8）

 use demo;

#代码开始
#设计相关实验用例数据，观察数据运行结果。
#（8）修改被参照表引用的主码值（将S表S5改为S7）
update s set sno="S7" where sno="S5";

#代码结束

第17关：级联删除、级联修改

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）

use demo;

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

delete from s where s.sno="S1";

#代码结束
select * from s;
select * from spj;

第19关：级联删除、级联修改测试（2）

use demo;

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

update s set s.sno="S7" where s.sno="S5";

#代码结束

select * from s;
select * from spj;