数据库完整性技术

本文主要是介绍数据库完整性技术，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、定义完整性。
/创建表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';

这篇关于数据库完整性技术的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！