实验三 数据库完整性

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

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）

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;