实验三 数据库完整性技术

create table S
(
Sno char(2) ,
Sname varchar(10) NOT NULL,
City char(6) default null,
primary key(Sno)
);

–创建p表
–定义p表； pno主码，pname非空、color只能取红、蓝、绿
create table P
(
Pno char(2),
Pname varchar(10) not null,
Color char(3) check(Color in(‘红’,‘蓝’,‘绿’)),
primary key(Pno)
);

–创建j表
–定义j表； jno主码， jname非空
create table J
(
Jno char(2),
Jname varchar(10) not null,
primary key(Jno)
);

–创建spj表
–定义spj表； (sno,pno,jno)主码，参照sno、pno、jno外码，qty介于0—1000
create table SPJ
(
Sno char(2),
Pno char(2),
Jno char(2),
Qty int ,
primary key(Sno,Pno,Jno),
foreign key(Sno) references S(Sno),
foreign key(Pno) references P(Pno),
foreign key(Jno) references J(Jno)
);
–插入合乎约束的数据
–向S表中插入数据
insert
into S(Sno,Sname,City)
values(‘S1’,‘竟仪’,‘天津’);

insert
into S(Sno,Sname,City)
values(‘S2’,‘盛锡’,‘北京’);

insert
into S(Sno,Sname,City)
values(‘S3’,‘东方红’,‘北京’);

insert
into S(Sno,Sname,City)
values(‘S4’,‘丰泰盛’,‘天津’);

insert
into S(Sno,Sname,City)
values(‘S5’,‘为民’,‘上海’);

–查询S表中的所有数据
select *
from S;

–向P表中插入有效数据
insert
into P(Pno,Pname,Color)
values(‘P1’,‘螺母’,‘红’);

insert
into P(Pno,Pname,Color)
values(‘P2’,‘螺栓’,‘绿’);

insert
into P(Pno,Pname,Color)
values(‘P3’,‘螺丝刀’,‘蓝’);

insert
into P(Pno,Pname,Color)
values(‘P4’,‘螺丝刀’,‘红’);

insert
into P(Pno,Pname,Color)
values(‘P5’,‘凸轮’,‘蓝’);

insert
into P(Pno,Pname,Color)
values(‘P6’,‘齿轮’,‘红’);

–查询p表中的所有数据
select *
from P;

–向J表中插入有效数据
insert
into J(Jno,Jname)
values(‘J1’,‘三建’);

insert
into J(Jno,Jname)
values(‘J2’,‘一汽’);

insert
into J(Jno,Jname)
values(‘J3’,‘弹簧厂’);

insert
into J(Jno,Jname)
values(‘J4’,‘造船厂’);

insert
into J(Jno,Jname)
values(‘J5’,‘机车厂’);

insert
into J(Jno,Jname)
values(‘J6’,‘无线电厂’);

insert
into J(Jno,Jname)
values(‘J7’,‘半导体厂’);
–查询J表中的所有数据
select *
from J;

–向SPJ表中插入有效数据
insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,‘P1’,‘J1’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,‘P1’,‘J3’,100);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,‘P1’,‘J4’,700);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,‘P2’,‘J2’,100);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P3’,‘J1’,400);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P3’,‘J2’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P3’,‘J4’,500);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P3’,‘J5’,400);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P5’,‘J1’,400);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S2’,‘P5’,‘J2’,100);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S3’,‘P1’,‘J1’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S3’,‘P3’,‘J1’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S4’,‘P5’,‘J1’,100);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S4’,‘P6’,‘J3’,300);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S4’,‘P6’,‘J4’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S5’,‘P2’,‘J4’,100);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S5’,‘P3’,‘J1’,200);

insert into SPJ(Sno,Pno,Jno,Qty)values(‘S5’,‘P6’,‘J2’,200);

insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S5’,‘P6’,‘J4’,500);

–查询SPJ表中的所有数据
select *
from SPJ;

三、设计相关实验用例数据
1、针对下列情况，如果出错的话，给出错误码，并说明原因；
不出错的话，观察数据并说明理由。

（1）插入违反约束的元组，主码为null值
insert
into S(Sno,Sname,City)
values(null,‘张三’,‘湖北’);

（2）插入违反约束的元组，主码取重复值
insert
into S(Sno,Sname,City)
values(‘S1’,‘张三’,‘湖北’);

（3）插入违反约束的元组，用户定义完整性（pname非空）
insert
into P(Pno,Pname,Color)
values(‘P7’,null,‘红’);

（4）插入违反约束的元组，用户定义完整性（color只能取红、蓝、绿）
insert
into P(Pno,Pname,Color)
values(‘P8’,‘螺丝刀’,‘黑’);

（5）插入违反约束的元组，外码取null值
insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,null,‘J1’,200);

（6）插入违反约束的元组，外码取对应主码没有的值
insert
into SPJ(Sno,Pno,Jno,Qty)
values(‘S1’,‘P9’,‘J3’,100);

（7）修改外码值，取对应主码已有的值
update SPJ
set Pno=‘P2’
where Sno=‘S1’ and Pno=‘P1’ and Jno=‘J3’;

select *
from SPJ;

（8）修改外码值，取对应主码没有的值
update SPJ
set Sno=‘S9’
where Sno=‘S1’ and Pno=‘P1’ and Jno=‘J3’;

（9）删除被参照表未引用的主码值
delete
from P
where Pno=‘P4’;

（10）删除被参照表(已被)引用的主码值
delete
from P
where Pno=‘P6’;

（11）修改被参照表未(被)引用的主码值
update P
set Pno=‘P4’
where Pno=‘P8’;

select *
from P;

（12）修改被参照表引用的主码值
update P
set Pno=‘P9’
where Pno=‘P6’;

2、将外码改成级联删除。
–删除SPJ的定义
drop table SPJ;

create table SPJ
(
Sno char(2),
Pno char(2),
Jno char(2),
Qty int constraint C1 check(Qty between 0 and 1000),
constraint SPJKey primary key(Sno,Pno,Jno),
constraint FK_Sno foreign key(Sno) references S(Sno) on delete cascade,
constraint FK_Pno foreign key(Pno) references P(Pno) on delete cascade,
constraint FK_Jno foreign key(Jno) references J(Jno) on delete cascade
);

select * from SPJ;
3、在2的基础上，插入数据。针对下列情况，如果出错的话，给出错误码，并说明原因；
不出错的话，观察数据并说明理由。
（1）删除被参照表未(被)引用的主码值
delete
from P
where Pno=‘P4’;

select *
from　P;

（2）删除被参照表引用的主码值
delete
from P
where Pno=‘P6’;

（3）修改被参照表未(被)引用的主码值
update P
set Pno=‘P4’
where Pno=‘P8’;

select *
from P;

（4）修改被参照表引用的主码值
update P
set Pno=‘P9’
where Pno=‘P6’;

select *
from P;