SQL 完整性约束

最新推荐文章于 2025-07-03 13:39:57 发布

原创最新推荐文章于 2025-07-03 13:39:57 发布 · 1.5w 阅读

58 ·

CC 4.0 BY-SA版权

SQL学习专栏收录该内容

15 篇文章

订阅专栏

完整性约束
1.掌握约束的主要作用；
2.掌握5中约束的使用形式；
3.理解约束的修改操作；
4.可以同坐数据字典表查看约束信息；

完整性约束是保证用户对数据库所做的修改不会破坏数据的一致性，是保护数据正确性和相容性的一种手段。

维护完整性
在一个DBMS之中，为了能够维护数据库的完整性，必须能够提供以下的几种支持：
提供定义完整性约束条件机制：在数据表上定义规则，这些规则是数据库中的数据必须满足的语义约束条件；
提供完整性检查的方法：在更新数据库时检查更新数据是否满足完整性约束条件；
违约处理：DBMS发现数据违反了完整性约束条件后要采取的违约处理行为，如拒绝（NO ACTION）执行该操作，或者级联（CASCADE）执行其他操作

主要约束分类在开发之中可以使用以下的五种约束进行定义：
非空约束：如果使用了非空约束的话，则以后此字段的内容不允许设置成null；
唯一约束：即：此列的内容不允许出现重复；
主键约束：表示一个唯一的标识，例如：人员ID不能重复，且不能为空；
检查约束：用户自行编写设置内容的检查条件；
主-外键约束（参照完整性约束）：是在两张表上进行的关联约束，加入关联约束之后就产生父子的关系。

1、非空约束：NK
在正常情况下，NULL是每个属性的合法数据值。如果说现在某个字段不能为NULL，且必须存在数据，那么就可以依靠非空约束来进行控制，这样在数据更新时，此字段的内容出现NULL时就会产生错

例：定义member表，其中姓名不允许为空
drop table member purge ;
create table member(mid number ,name varchar2(200) not null);

向member表中增加正确的数据
insert into member(mid,name) values (1,‘Ken’) ;
向member表中增加错误的数据（两种语句的执行结果一样）明确设置name字段为null：
insert into member(mid,name) values (3,null) ; 结果报错，无法将null插入
不设置name字段的内容：结果报错，无法将null插入
insert into member(mid) values (3) ;
非空约束不允许字段为null；
非空约束出现错误时会提示完整的错误位置
2、唯一约束
违反唯一约束报错信息：ora00001
唯一约束（UNIQUE，简称UK）表示的是在表中的数据不允许出现重复的情况，
例如：每一位成员都肯定有自己的email地址，而这个地址肯定是不重复的。 drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) unique);
唯一约束 —— 范例
为唯一约束指定一个名字
drop table member purge ;
create table member(mid number ,name varchar2(200) not null
,email varchar2(50) ,constraint uk_email unique (email));
创建member表，在email字段上设置唯一约束
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) unique)
向member表中增加正确记录
insert into member (mid,name,email) values(1,‘Tony’,‘tt@163.com’) ;
commit；
向member表中增加错误的纪录
insert into member (mid,name,email) values(2,‘Mark’,‘tt@163.com’) ;
ORA-00001: 违反唯一约束条件 (SPORT.SYS_C0011403)
为唯一约束指定一个名字
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50)
,constraint uk_email unique (email)) ;
此时，再次执行两条插入语句，则错误提示将变为以下的内容
插入两条包含null的记录
insert into member (mid,name,email) values(10,‘vdata’,null) ;

insert into member (mid,name,email) values(20,‘enmo’,null) ;

select * from member;
发现两条均已插入
小结
唯一约束可以设置NULL；
唯一约束的列不允许重

3、主键约束：PK
如果一个字段即要求唯一，又不能设置为null，则可以使用主键约束（主键约束 = 非空约束 + 唯一约束），主键约束使用PRIMARY KEY（简称PK）进行指定，
例如：在member表中的mid字段应该表示一个成员的唯一编号，而这个编号即不能为空，也不能重复
设置member表中的mid为主键
drop table member purge ;
create table member(mid number primary key ,name varchar2(200) not null ,email varchar2(50)
,constraint uk_ email unique (email));

主键约束 —— 范例
指定主键约束的名称
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) ,
constraint pk_mid primary key (mid),constraint uk_email unique (email));

复合主键
在实际的开发之中，一般在一张表中只会设置一个主键，但是也允许为一张表设置多个主键，这个时候将其称为复合主键。
在复合主键中，只有两个主键字段的内容完全一样，才会发生违反约束的错误。
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) ,
constraint pk_mid_name primary key (mid,name) ,constraint uk_email unique (email)) ;

设置member表中的mid为主键
drop table member purge ;
create table member(mid number primary key ,name varchar2(200) not null ,email varchar2(50)
,constraint uk_email unique (email)) ;

范例：将mid设置为null
insert into member(mid,name,email) values(null,
‘Tony’,‘Tony@163.com’);
错误报告 -
ORA-01400: 无法将 NULL 插入 (“SPORT”.“MEMBER”.“MID”)

范例：插入重复的mid
insert into member (mid,name,email) values(1,‘Ken’,‘Ken@163.com’) ;

insert into member (mid,name,email) values(1,‘Mark’,‘Mark@163.com’);
错误报告 -
ORA-00001: 违反唯一约束条件 (SPORT.SYS_C0011415)

范例：指定主键约束的名称
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) ,
constraint pk_mid primary key (mid) ,constraint uk_email unique (email));

范例：将mid和name两个字段同时设置为主键
drop table member purge ;
create table member(mid number ,name varchar2(200) not null ,email varchar2(50) ,
constraint pk_mid_name primary key (mid,name) ,constraint uk_email unique(email)) ;

范例：插入正确数据
insert into member (mid,name,email) values(1,‘Tony’,‘Tony@163.com’) ;
insert into member (mid,name,email) values(1,‘Mark’,‘Mark@163.com’);
范例：插入错误的数据 —— mid和name相同
insert into member (mid,name,email) values(1,‘Tony’,‘Tony@163.com’);
错误报告 -
ORA-00001: 违反唯一约束条件 (SPORT.PK_MID_NAME)
范例：查看member表数据
select * from member ;
小结
主键约束 = 非空约束 + 唯一约束；
复合主键约束一般不建议使用，尽量避免使用

4、检查约束
检查约束指的是对数据增加的条件过滤，表中的每行数据都必须满足指
定的过滤条件。在进行数据更新操作时，如果满足检查约束所设置的
条件，数据可以成功更新，如果不满足，则不能更新，在SQL语句中
使用CHECK（简称CK）设置检查约束的条件。

检查约束 —— 范例
在member表中增加age字段（年龄范围是0~200岁）和sex字段（只能是男或女）
drop table member purge ;
create table member(mid number ,name varchar2(200) not null
,email varchar2(50) ,age number check (age between 0 and 200)
,sex varchar2(10) ,constraint pk_mid_name primary key (mid,name) ,
constraint uk_email unique (email) ,
constraint ck_sex check (sex in (‘男’,‘女’)));
select * from member;
insert into member values(1,‘asd’,‘asd@163.com’,66,‘男’);
insert into member values(1,‘asd’,‘asd@163.com’,6666,‘男’);
报错ORA-02290: 违反检查约束条件 (SCOTT.SYS_C0011363)。

小结
检查约束会设置多个过滤条件，所以检查约束过多时会影响数据更新

5、外键约束的产生分析
例如，现在公司要求每一位成员为公司发展提出一些更好的建议，并且希望将这些建议保存在数据表之中，那么根据这样的需求，可以设计出如图所示的设计模型。

设计出了两张数据表，两张表的作用如下：
人员表：用于保存成员的基本信息（编号、姓名）
建议表：保存每一个成员提出的建议内容，所以在此表之中保存在了一个成员编号，即：通过此成员编号就可以和成员表进行数据的关联
：根据给出的数据模型编写数据库创建脚本
drop table member purge ;
drop table advice purge ;
create table member (mid number ,name varchar2(200) not null ,
constraint pk_mid primary key (mid)) ;
create table advice (adid number ,content clob not null ,mid number
,constraint pk_adid primary key (adid)) ;
插入正确的数据 —— 向member表插入两个会员信息
insert into member (mid,name) values (1,‘Tony’) ;
insert into member (mid,name) values (2,‘Mark’) ;
commit ;
select * from member;
插入正确的数据 —— 向advice表插入五条新记录
insert into advice (adid,content,mid) values (1,‘应该提倡内部沟通机制，设置总裁邮箱’,1) ;
insert into advice (adid,content,mid) values (2,‘为了使公司内部良性发展，所有的部门领导应该重新应聘上岗’,1) ;
insert into advice (adid,content,mid) values (3,‘要多开展员工培训活动，让员工更加有归属感’,1) ;
insert into advice (adid,content,mid) values (4,‘应该开展多元化业务，更加满足市场需求’,2) ;
insert into advice (adid,content,mid) values (5,‘大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求’,2) ;
commit；
select * from advice;
查询出每位成员的完整信息以及所提出的意见数量
select m.mid,m.name,count(a.mid) from member m,advice a
where m.mid=a.mid group by m.mid,m.name ;
在意见表（advice）中增加以下错误的信息
insert into advice (adid,content,mid) values (6,‘岗位职责透明化’
,99) ;
select * from advice ;
修改表结构，指定主-外键约束
drop table member purge ;
drop table advice purge ;
create table member (mid number ,name varchar2(200) not null ,constraint pk_mid primary key (mid)) ;
create table advice (adid number ,content clob not null ,mid number ,constraint pk_adid primary key (adid) ,
constraint fk_ mid foreign key(mid) references member(mid);

范例：向advice表中插入错误的数据 —— 此时member表中没有mid=99的数据
insert into advice (adid,content,mid) values (6,‘岗位职责透明化’,99) ;
范例：插入正确的数据
insert into member (mid,name) values (1,‘Tony’) ;
insert into advice (adid,content,mid) values (1,‘应该提倡内部沟通机制，设置总裁邮箱’,1) ;
insert into advice (adid,content,mid) values (3,‘要多开展员工培训活动，让员工更加有归属感’,2) ;
commit ;

级联操作删除数据 rollback可以恢复？
级联操作
问题一：删除父表数据前需要先清出所有子表的对应数据【级联操作一】级联删除（ON DELETE CASCADE）【级联操作二】级联设置NULL（ON DELETE SET NULL）
问题二：删除父表时需要先将子表删除

范例：删除member表中编号为“1”的数据（mid=1），此时没有删除子表（advice）数据
delete from member where mid=1 ;
范例：先删除子表（advice）中mid=1的数据，之后再删除父表（member）中mid=1的数据
delete from advice where mid=1 ;
delete from member where mid=1 ;
commit ;
范例：查询member表中的记录
SELECT * FROM member

范例：修改表创建语法，增加级联删除，同时配置测试数据
drop table advice purge ;
drop table member purge ;
create table member (mid number ,name varchar2(200) not null ,
constraint pk_mid primary key (mid)) ;
create table advice (adid number ,content clob not null ,mid number ,
constraint pk_adid primary key (adid) ,
constraint fk_mid foreign key(mid) references member(mid) on delete cascade);

insert into member (mid,name) values (1,‘Tony’) ;
insert into member (mid,name) values (2,‘Mark’) ;
insert into advice (adid,content,mid) values (1,
‘应该提倡内部沟通机制，设置总裁邮箱’,1) ;
insert into advice (adid,content,mid) values (3,
‘要多开展员工培训活动，让员工更加有归属感’,2) ;
commit;
范例：查询member表中的当前数据
select * from member ;
范例：查询advice表中的当前数据
select * from advice ;
范例：删除member表中编号为1的成员信息
delete from member where mid=1 ;
范例：查询member表记录
select * from member ;
范例：查询advice表记录
select * from advice;

范例：修改表的创建语句，增加on delete set null子句
drop table advice purge ;
drop table member purge ;
create table member (mid number ,name varchar2(200) not null ,
constraint pk_mid primary key (mid)) ;
create table advice (adid number ,content clob not null ,mid number ,
constraint pk_adid primary key (adid) ,
constraint fk_mid foreign key(mid) references member(mid) on delete set null);

范例：修改表的创建语句，增加on delete set null子句
insert into member (mid,name) values (1,‘Tony’) ;
insert into member (mid,name) values (2,‘Mark’) ;
insert into advice (adid,content,mid) values (1,‘应该提倡内部沟通机制，设置总裁邮箱’,1) ;
insert into advice (adid,content,mid) values (3,‘要多开展员工培训活动，让员工更加有归属感’,2) ;
commit ;

范例：查询member表中的当前数据
select * from member ;
范例：查询advice表中的当前数据
select * from advice ;
范例：删除member表中编号为1的成员信息
delete from member where mid=1 ;
范例：查询member表记录
select * from member ;
范例：查询advice表记录
select * from advice;

主外键表的删除操作

范例：直接删除父表（member）
drop table member ;
错误报告 -
ORA-02449: 表中的唯一/主键被外键引用
范例：先删除子表（advice），再删除父表（member）。
drop table advice purge ;
drop table member purge ;
范例：强制性删除member表
drop table member cascade constraint;

小结
级联操作：
on delete cascade、on delete set null；

使用外键约束后删除表时应先删除子表再删除父

查看约束 —— 范例

查看全部的约束名称、类型、约束设置对应的表名称
select constraint_name,constraint_type,table_name from user_constraints;
在这里插入图片描述

查询emp表上的全部约束
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name=‘EMP’;
在这里插入图片描述
查询user_cons_columns数据字典
select * from user_cons_columns;

小结
约束依然属于数据库对象，可以直接利用数据字典查。

增加约束
为表中增加约束语法：
alter table 表名称 add constraint 约束名称 primary key(约束字段) ;
范例：为member表的mid字段增加主键约束
alter table member add constraint pk_mid primary key(mid) ;
范例：为member表的age增加检查约束
alter table member add constraint ck_age check(age between 0 and 200)；
在这里插入图片描述

范例：假设有如下的一张表
drop table member purge ;
create table member(mid number ,name varchar2(30) ,age number) ;
范例：查看member表中的约束
select constraint_name,constraint_type,table_name from user_constraints
where table_name=‘MEMBER’ ;
范例：为member表的mid字段增加主键约束
alter table member add constraint pk_mid primary key(mid) ;
范例：为member表的age增加检查约束
alter table member add constraint ck_age check(age between 0 and 200) ;
为表中添加完约束之后，下面可以通过数据字典user_constraints查看member表中的全部约束。
范例：查看member表中的约束
select constraint_name,constraint_type,table_name from user_constraints where table_name=‘MEMBER’ ;

范例：为name字段设置非空约束
alter table member modify (name varchar2(30) not null)；
在这里插入图片描述

启用／禁用约束
启用／禁用约束
禁用约束
ALTER TABLE 表名称 DISABLE CONSTRAINT 约束名称 [CASCADE];
范例：禁用advice表中的adid主键约束“pk_adid”
ALTER TABLE advice DISABLE CONSTRAINT pk_adid ;
启用约束
ALTER TABLE 表名称 ENABLE CONSTRAINT 约束名称 ;
范例：禁用member表中的“pk_mid”约束，此字段在advice表中是外键
ALTER TABLE member ENABLE CONSTRAINT pk_mid

范例：给出要操作的数据表
drop table advice purge ;
drop table member purge ;
create table member (mid number ,name varchar2(200) not null ,
constraint pk_mid primary key (mid)) ;
create table advice (adid number ,content clob not null ,mid number ,
constraint pk_adid primary key (adid) ,
constraint fk_mid foreign key(mid) references member(mid) on delete set null);
insert into member (mid,name) values (1,‘Tony’) ;
insert into member (mid,name) values (2,‘Mark’);
insert into advice (adid,content,mid) values (1,‘应该提倡内部沟通机制，设置总裁邮箱’,1) ;
insert into advice (adid,content,mid) values (2,‘为了使公司内部良性发展，所有的部门领导应该重新应聘上岗’,1) ;
insert into advice (adid,content,mid) values (3,‘要多开展员工培训活动，让员工更加有归属感’,1) ;
insert into advice (adid,content,mid) values (4,‘应该开展多元化业务，更加满足市场需求’,2) ;
insert into advice (adid,content,mid) values (5,‘大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求’,2) ;
commit;
范例：禁用advice表中的adid主键约束“pk_adid”
alter table advice disable constraint pk_adid;
范例：查询禁用约束之后advice表中的数据
select * from advice ;
范例：禁用member表中的“pk_mid”约束，此字段在advice表中是外键
alter table member disable constraint pk_mid cascade;
范例：增加两条mid相同的数据
insert into member(mid,name) values (1,‘Tom’) ;
insert into member(mid,name) values (1,‘Jack’) ;
范例：查询member表中的记录
select * from member ;
范例：重新启动member表中的主键约束“pk_mid”
alter table member enable constraint pk_mid ;
范例：重新启动advice表中的主键约束“pk_adid”
alter table advice enable constraint pk_adid；

删除约束
删除约束
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 [CASCADE];
范例：删除advice表之中的“pk_adid”约束 —— 无关联外键
ALTER TABLE advice DROP CONSTRAINT pk_adid ;
范例：删除member表之中的“pk_mid”约束 —— 有关联外键
ALTER TABLE member DROP CONSTRAINT pk_mid CASCADE；

小结
约束在建立表的时候一定要同时建立；对于约束不建议对其进行修