实验二-数据库完整性技术.doc

数据库系统原理实验报告
实验二 数据库完整性技术
班级： 姓名： 学号：
任课教师： 实验教师： 上机时间：
【实验目的】
1、掌握完整性的概念；
2、熟悉Oracle的完整性技术。
3、了解Oracle的违反完整性处理措施。
【实验性质】
验证性实验
【实验学时】
2H
【实验内容】
写出完整、详尽的SQL语句，根据实验记录结果并总结。(空位不够请自行续页)
一、定义完整性

/*创建表sp、j、spj*/
定义s表； sno主码，sname非空、city缺省值
定义p表； pno主码，pname非空、color只能取红、蓝、绿
定义j表； jno主码， jname非空
定义spj表； (sno,pno,jno)主码，参照sno、pno、jno外码，qty介于0—1000
drop table s;
drop table p;
drop table j;
drop table spj;

create table s
(sno char(2) constraint pk_s_sno primary key,
sname varchar2(8) constraint nn_s_sname not null,
status int,
city varchar2(8) default('天津')
);

create table p
(pno char(2)constraint pk_p_pno primary key,
pname varchar2(8) constraint nn_p_pname not null,
color char(2) constraint c_p_color check(color in('红','蓝','绿')),
weight int
);

create table j
(jno char(2) constraint pk_j_jno primary key,
jname varchar2(8)constraint nn_j_jno not null,
city varchar2(8)
);

create table spj
(sno char(2),
pno char(2),
jno char(2),
qty int,
constraint pk_spj 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),
constraint ck_spj_qty check (qty>0 and qty<1000)
);

insert into s values ('s1','竟仪',20,'天津');
insert into s values ('s2','盛锡',10,'北京');
insert into s values ('s3','东方红',30,'北京');
insert into s values ('s5','为民',30,'上海');

insert into p values ('p1','螺母','红',12);
insert into p values ('p2','螺栓','绿',17);
insert into p values ('p3','螺丝刀','蓝',14);
insert into p values ('p4','螺丝刀','蓝',14);

insert into j values('j1','三建','北京');
insert into j values('j2','一汽','长春')
insert into j values('j3','弹簧厂','天津');
insert into j values('j4','造船厂','天津');
insert into j values('j5','机车厂','唐山');

insert into spj values('s1','p1','j4',700);
insert into spj values('s1','p2','j2',100);
insert into spj values('s2','p3','j1',400);
insert into spj values('s2','p3','j2',200);
insert into spj values('s3','p1','j1',200);
insert into spj values('s3','p3','j1',200);

1、针对下列情况，如果出错的话，给出错误码，并说明原因；不出错的话，观察数据并说明理由。
（1）插入违反约束的元组，主码为null值
insert into s values (null,'竟仪',20,'天津');
ora-01400:主码不能为空值

（2）插入违反约束的元组，主码取重复值
insert into s values 's1','竟仪',20,'天津');
ora-00001:违反主码唯一性原则，主码不能有重复的

（3）插入违反约束的元组，用户定义完整性（pname非空）
insert into p values ('p4',null,'红',12);
ora-01400:pname不能插入空值

（4）插入违反约束的元组，用户定义完整性（color只能取红、蓝、绿）
insert into p values ('p5','螺母','黄',12);
ora-02290:color只能取红、蓝、绿

5）插入违反约束的元组，外码取null值
insert into spj values(null,'p1','j4',700);
ora-01400:外码不能取空值

（6）插入违反约束的元组，外码取对应主码没有的值
insert into spj values('s4','p1','j4',700);
ora-02291:主码不能取父表没有的值

（7）修改外码值，取对应主码已有的值
update spj set sno='s1'where sno='s2';
select * from spj
（8）修改外码值，取对应主码没有的值
update spj set sno='s4'where sno='s1';
ora-02291:父表找不到的值
（9）删除被参照表未引用的主码值
select * from s
delete from s where sno='s5';
（10）删除被参照表引用的主码值
delete from s where sno='s1';
ora-02292:
（11）修改被参照表未引用的主码值
update p set pno='p2'where pno='p4';
ora-00001:
（12）修改被参照表引用的主码值
update p set pno='p6'where pno='p3';
ora-02292:违反完整约束条件
2、将外码改成级联删除。
alter table spj drop constraint fk_spj_sno;
alter table spj drop constraint fk_spj_pno;
alter table spj drop constraint fk_spj_jno;

alter table spj add constraint fk_spj_sno foreign key(sno) references s(sno) on delete cascade;
alter table spj add constraint fk_spj_pno foreign key(pno) references p(pno) on delete cascade;
alter table spj add constraint fk_spj_jno foreign key(jno) references jjno) on delete cascade;

3、在2的基础上，插入数据。针对下列情况，如果出错的话，给出错误码，并说明原因；不出错的话，观察数据并说明理由。
（1）删除被参照表未引用的主码值
delete from p where pno='p4';
（2）删除被参照表引用的主码值
delete from j where jno='j4';

【实验总结】
请根据实验内容谈谈你对本次实验的收获、感想，或提出你对实验内容的建议等等。

批改教师评语：

实验报告成绩：

批改教师签字

批改时间：

6