数据库完整性技术

一、定义完整性。
/创建表s、p、j、spj/
定义s表; sno主码,sname非空、city缺省值
定义p表; pno主码,pname非空、color只能取红、蓝、绿
定义j表; jno主码, jname非空
定义spj表; (sno,pno,jno)主码,参照sno、pno、jno外码

 create table s
(
sno        char(2) PRIMARY KEY,
sname      varchar(6) NOT NULL,
status     int,
city       varchar(6) DEFAULT '天津'
);


create table p(
pno      char(2) PRIMARY KEY,
pname    varchar(6) NOT NULL,
color    enum('红','蓝','绿'),
weight   int
);


create table j
(
jno        char(2) PRIMARY KEY,
jname      varchar(8) NOT NULL,
city       varchar(6)
);

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)
);

二、插入合乎约束的数据。

insert into s values ('S1','竟仪',20,'天津');
insert into s values('S2','盛锡',10,'北京');
insert into s values('S3','东方红',30,'北京');
insert into s values('S4','丰泰盛',20,'天津');
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 p values('P5','凸轮','蓝',40);
insert into p values('P6','齿轮','红',30);

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 j values('J6','无线电厂','常州');
insert into j values('J7','半导体厂','南京');

insert into spj values('S1','P1','J1',200);
insert into spj values('S1','P1','J3',100);
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('S2','P3','J4',500);
insert into spj values('S2','P3','J5',400);
insert into spj values('S2','P5','J1',400);
insert into spj values('S2','P5','J2',100);
insert into spj values('S3','P1','J1',200);
insert into spj values('S3','P3','J1',200);
insert into spj values('S4','P5','J1',100);
insert into spj values('S4','P6','J3',300);
insert into spj values('S4','P6','J4',200);
insert into spj values('S5','P2','J4',100);
insert into spj values('S5','P3','J1',200);
insert into spj values('S5','P6','J2',200);
insert into spj values('S5','P6','J4',500);

1、针对下列情况,如果出错的话,说明出错原因;不出错的话,观察数据运行结果。
(1)插入违反约束的元组,主码为null值

insert into s values (null,'竟仪',20,'天津');

错误:主码不能为空
(2)插入违反约束的元组,主码取重复值

insert into s values ('S1','竟仪',20,'天津');

错误:主码不能取重复值
(3)插入违反约束的元组,用户定义完整性(pname非空)

insert into p values('P1',null,'红',12);

错误:pname不能为空
(4)插入违反约束的元组,用户定义完整性(color只能取红、蓝、绿)

insert into p values('P1','螺母','黑',12);

错误:color只能取红、蓝、绿枚举里面的值

(5)插入违反约束的元组,外码取null值

insert into spj values(null,'P1','J1',200);

错误:外码不能为空
(6)插入违反约束的元组,外码取对应主码没有的值

insert into spj values('S6','P1','J1',200);

错误:外码中没有S6这个主码
(7)修改外码值,取对应主码已有的值

update spj set pno='P2' WHERE sno='S1' AND pno='P1' AND jno='J1' 

(8)修改外码值,取对应主码没有的值

update spj set pno='P20' WHERE sno='S1' AND pno='P1' AND jno='J1'

错误:外码不能取主码没有的值
(9)删除被参照表未引用的主码值

DELETE FROM s WHERE sno='S3'

(10)删除被参照表引用的主码值

DELETE FROM s WHERE sno='S1'

错误:主码被参照表引用,不能被删除

(11)修改被参照表未引用的主码值

update s set sno='s3' WHERE sno='s4'

(12)修改被参照表引用的主码值

update s set sno='S2' WHERE Sno='S1';

错误:主码被参照表引用,不能被修改

2、将外码改成级联删除、级联修改。(5分)

alter table spj drop FOREIGN KEY fk_spj_sno;
alter table spj drop FOREIGN KEY fk_spj_jno;
alter table spj drop FOREIGN KEY fk_spj_pno;

ALTER table spj add CONSTRAINT fk_spj_sno FOREIGN key (sno) REFERENCES s(sno) on DELETE CASCADE on UPDATE CASCADE
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

3、在2的基础上,插入数据。针对下列情况,如果出错的话,给出错误码,并说明原因;不出错的话,观察数据并说明理由。(共20分,每题5分)
(1)删除被参照表未引用的主码值

DELETE FROM s WHERE sno='S4';

(2)删除被参照表引用的主码值

DELETE FROM s WHERE sno='S3';

(3)修改被参照表未引用的主码值

UPDATE s SET sno='S4' WHERE sno='S3';

(4)修改被参照表引用的主码值

UPDATE s SET sno='S4' WHERE sno='S3';
上一篇:记一次数据库课后作业:SQL查询应用


下一篇:SPJ数据库-初始sql语句(5)(注释版)