数据库完整性的概念与实践-优快云博客

本文链接：https://blog.youkuaiyun.com/ArithmeticException/article/details/53222805

本文深入探讨了数据库完整性的重要性，介绍了实体完整性、参照完整性和用户定义的完整性三大原则，并结合实际案例阐述如何在数据库设计中实施这些完整性约束，确保数据的准确性和一致性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--一、定义完整性

--创建s表
--定义s表； sno主码，sname非空、city缺省值
create table S
(
       Sno char(2),
       Sname varchar2(10),
       City char(6)  default null,
       primary key(Sno)
);

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

--创建j表
--定义j表； jno主码， jname非空
create table J
(
       Jno char(2),
       Jname varchar2(15) 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 check(Qty between 0 and 1000),
       primary key(Sno,Pno,Jno),
       foreign key(Sno) references S(Sno),
       foreign key(Pno) references P(Pno),
       foreign key(Jno) references J(Jno)
);

--创建可以级联删除的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
);

--删除S表
drop table S;
--删除P表
drop table P;
--产出J表
drop table J;
--删除SPJ表
drop table SPJ;

二、插入数据
--插入合乎约束的数据
--向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','齿轮','红');

--查询S表中的所有数据
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,'竟仪','上海');
--提示错误ORA-01400:无法将NULL插入("SYSTEM","S","SNO")
--这是因为:S表中Sno为主码,插入的数据中主码值为null,违反了实体的完整性,
--主码值不能为空,关系型数据库管理系统拒绝插入

（2）插入违反约束的元组，主码取重复值
insert
into S(Sno,Sname,City)
values('S1','竟仪','上海');
--提示错误ORA-00001:违反唯一约束条件(SYSTEM.SYS_C0011202)
--这是因为:S表中的主码为Sno,插入的Sno的值和S表中第一个元组的Sno的值相同
--违反了实体的完整性,主码值不能重复,关系型数据库管理系统拒绝插入

（3）插入违反约束的元组，用户定义完整性（pname非空）
insert
into P(Pno,Pname,Color)
values('P7',null,'红');
--错误提示ORA-01400:无法将NULL值插入("SYSTEM","P","PNAME")
--这是因为:在定义P表时,Pname用户定义约束条件为not null,而插入数据时Pname
--的值为空,违反了用户定义完整性


（4）插入违反约束的元组，用户定义完整性（color只能取红、蓝、绿）
insert
into P(Pno,Pname,Color)
values('P8','螺丝刀','黑');
--错误提示ORA-02290:违反检查约束条件(SYSTEM.SYS_C0011214)
--这是因为:在定义P表时,Color属性列用户定义约束条件为check(Color in('红','绿','蓝'))
--插入元组时,Color属性的值为黑,违背了用户定义的完整性

（5）插入违反约束的元组，外码取null值
insert
into SPJ(Sno,Pno,Jno,Qty)
values('S1',null,'J1',200);
--错误提示ORA-01400:无法将NULL插入("SYSTEM","SPJ","PNO")
--这是因为:在定义SPJ表的时候,SPJ表中的Pno是SPJ表中的外码,通常情况下外码是可以取空值的
--但是在SPJ表中Pno同时还是主码的一部分,组成主码的属性列都是不能去空值的,违反了实体的完整性

（6）插入违反约束的元组，外码取对应主码没有的值
insert
into SPJ(Sno,Pno,Jno,Qty)
values('S1','P9','J3',100);
--错误提示ORA-02291:违反完整性约束条件(SYSTEM.SYS_C0011219)-未找到父项关键字
--这是因为:在定义SPJ表时Pno是外码,参考自P表中的Pno属性值,也就是说SPJ表中的Pno属性值
--只能够取被参照表P表中Pno已有的属性值,这里Pno取了被参照表P表中Pno属性没有的值,
--违反了参照的完整性,导致插入失败.

（7）修改外码值，取对应主码已有的值
update SPJ
set Pno='P2'
where Sno='S1' and Pno='P1' and Jno='J3';
--执行成功:修改外码值的时候,修改成被参照表中对应被参照属性的已有的值是可以的,
--在更新操作SQL语句没有写错的情况下是可以的.

（8）修改外码值，取对应主码没有的值
update SPJ
set Sno='P9'
where Sno='S1' and Pno='P1' and Jno='J3';
--错误提示ORA-02291:违反了完整约束条件(SYSTEM.SYS_C0011218)-未找到父项关键字
--这是因为:Sno为SPJ表中的外码,参照表S表中的Sno属性,外码值只能取被参照表中对应被
--参照属性已有的取值,而不能取被参照表中对应被参照属性没有的值,这里违反了参照的完整性

（9）删除被参照表未引用的主码值
delete
from P
where Pno='P4';
--删除成功,因为P表中的Pno='P4',没有被参照表中的Pno所参照,在删除表中的元组的SQL语句没有错误
--的情况下,是可以直接执行成功的,如果P表中的Pno='P4'被参照表中的Pno所参照,在删除P表中的Pno=4
--的元组时,关系型数据库管理系统默认处理方式是拒绝执行

（10）删除被参照表(已被)引用的主码值
delete
from P
where Pno='P6';
--错误提示ORA-02292:违反完整约束条件(SYSTEM.SYS_C0011219)-已找到子记录
--P表中的Pno='P6'被参照表中的Pno所参照,在删除被参照表P表中的Pno='P6'对应
--的元组时,关系型数据库管理系统默认处理方式是拒绝执行

（11）修改被参照表未(被)引用的主码值
update P
set Pno='P8'
where Pno='P4';
--执行成功
--这是因为P表中的Pno='P4',没有被参照表中的Pno所参照,在更新表中的元组的SQL语句没有错误
--的情况下,是可以直接执行成功的

（12）修改被参照表引用的主码值
update P
set Pno='P9'
where Pno='P6';
--错误提示ORA-02292:违反了完整性约束条件(SYSTEM.SYS_C00011219)-已找到子记录
--P表中的Pno='P6'被参照表中的Pno所参照,在修改被参照表P表中的Pno='P6'对应
--的元组时,关系型数据库管理系统默认处理方式是拒绝执行

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

--创建基本表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
);

--将需要修改成级联删除的外键约束从SPJ表中删除



--查询SPJ表中的所有数据
select * from SPJ;
3、在2的基础上，插入数据。针对下列情况，如果出错的话，给出错误码，并说明原因；
   不出错的话，观察数据并说明理由。
（1）删除被参照表未(被)引用的主码值
delete
from P
where Pno='P4';
--成功删除:被参照表中的Pno='P4'这个属性值在参照表SPJ中的外码Pno中没有被参照,因此
--在删除SQL语句没有出错的情况下是可以直接删除被参照表中的指定元组的.
select *
from　P;

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


--成功删除:这是因为我们在定义SPJ表时,指定了外码值为级联删除,当删除被参照表中的
--已被引用的属性值对应的元组时,这时候因为是级联删除,首先会删除参照表中的引用的
--的属性值对应的元组,然后在删除被参照表中的指定的元组.
select *
from SPJ;

插入的数据
('S1','竟仪',20,'天津')
('S2','盛锡',10,'北京')
('S3','东方红',30,'北京')
('S4','丰泰盛',20,'天津')
('S5','为民',30,'上海')

('P1','螺母','红',12)
('P2','螺栓','绿',17)
('P3','螺丝刀','蓝',14)
('P4','螺丝刀','红',14)
('P5','凸轮','蓝',40)
('P6','齿轮','红',30)

('J1','三建','北京')
('J2','一汽','长春')
('J3','弹簧厂','天津')
('J4','造船厂','天津')
('J5','机车厂','唐山')
('J6','无线电厂','常州')
('J7','半导体厂','南京')

('S1','P1','J1',200)
('S1','P1','J3',100)
('S1','P1','J4',700)
('S1','P2','J2',100)
('S2','P3','J1',400)
('S2','P3','J2',200)
('S2','P3','J4',500)
('S2','P3','J5',400)
('S2','P5','J1',400)
('S2','P5','J2',100)
('S3','P1','J1',200)
('S3','P3','J1',200)
('S4','P5','J1',100)
('S4','P6','J3',300)
('S4','P6','J4',200)
('S5','P2','J4',100)
('S5','P3','J1',200)
('S5','P6','J2',200)
('S5','P6','J4',500)