Oracle完整性约束

程序员文章站 2022-06-02 13:16:34

...

一、完整性约束简介

1、完整性约束：

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

如果用户输入年龄，则年龄肯定不能是999。

如果用户输入性别，则性别的设置只能是 “男” 或 “女” ，而不是设置成 “未知”。

身份证号码的长度，只能是15位或者18位。

2、维护完整性约束：

在一个DBMS之中，为了能够维护数据库的完整性，必须能够提供以下的几种支持：

提供定义完整性约束条件机制：在数据表上定义规则，这些规则是数据库中的数据必须满足的语义约束条件。

提供完整性检查的方法：在更新数据库时检查更新数据是否满足完整性约束条件。

违约处理：DBMS发现数据违反了完整性约束条件后要采取的违约处理行为，如拒绝（NO ACTION）执行该操作，或者级联（CASCADE）执行其他操作。

3、主要约束分类：

非空（NOT NULL）约束：顾名思义，所约束的列不能为NULL值，否则就会报错。

唯一（UNIQUE）约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违法约束条件。

主键（PRIMARY KEY）约束：唯一的标识表中的每一行，不能重复，不能为空。创建主键或唯一约束后，ORACLE会自动创建一个与约束同名的索引（UNIQUENES为UNIQUE唯一索引）。需要注意的是：每个表只能有且有一个主键约束。

检查（CHECK）约束：用来约束表中列的输入值得范围，比如在输入性别时，要求数据库中只能输入男或者女，就可以使用检查约束来约束该列。

外键（FOREIGN KEY）约束：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性。

默认（DEFAULT）约束：数据库系统有一个隐式的默认值，如果命令没有显式给指定的列赋值，那么把默认约束值插入到该列中。

二、Oracle 常用的五大约束

1、非空约束（NOT NULL）

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

实例：

<1>、在创建 “member” 表时，强制 ‘name’ 不接受 null 值：

CREATE TABLE member 
(
    mid NUMBER,
    name VARCHAR(20) NOT NULL
);

By the way：

添加 NOT NULL 约束—————— ALTER TABLE 表名 MODIFY 字段名 NOT NULL;
删除 NOT NULL 约束—————— ALTER TABLE 表名 MODIFY 字段名 NULL;

<2>、为已创建的表 “member” 中的 “mid” 列添加非空约束：

alter table member modify mid not null;

<3>、撤销 “mid” 列中的非空约束：

alter table member modify mid null;

<4>、插入数据：

--正确
INSERT INTO member VALUES(1,'悟空');

--错误
INSERT INTO member VALUES(1,null);
--错误信息：无法将 NULL 插入 ("C##SCOTT"."MEMBER"."NAME")

2、唯一约束（UNIQUE）

唯一约束表示表中标定的数据不允许出现重复的情况。与主键约束不同的是，在一个数据表中可以有多个唯一约束。

实例：

<1>、在创建 “member” 表时，在 “email” 列上创建 UNIQUE 约束：

CREATE TABLE member 
(
     mid NUMBER,
     name VARCHAR(100) NOT NULL,
     email VARCHAR(50), 
     constraint uk_member_email UNIQUE (email)
);

By the way：

添加 unique 约束———— ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
解除 unique 约束———— ALTER TABLE 表名 DROP CONSTRAINT 约束名;

<2>、为已创建的表 “member” 中的 “mid” 列添加唯一约束：

alter table member add constraint uk_member_mid unique (mid);

<3>、撤销 “mid” 列中的唯一约束：

alter table member drop constraint uk_member_mid;

<4>、插入数据

--正确
INSERT ALL
INTO member VALUES(1,'悟空','[email protected]')
INTO member VALUES(1,'悟空','[email protected]')
SELECT * FROM DUAL;
--错误
INSERT ALL
INTO member VALUES(1,'悟空','[email protected]')
INTO member VALUES(2,'佛祖','[email protected]')
SELECT * FROM DUAL;
--错误信息：违反唯一约束条件(C##SCOTT.UK_MEMBER_EMAIL)

3、主键约束（PRIMARY KEY）

如果一个字段既要求唯一又不能设置为 null，则可以使用主键约束（主键约束 = 非空约束 + 唯一约），每个表都应该有一个主键，并且每个表只能有一个主键，但是一个主键约束可以包含多个列。

实例：

<1>、在创建 “member” 表时，在 “email” 列上创建主键约束：

CREATE TABLE member 
(
     mid NUMBER,
     name VARCHAR(100) NOT NULL,
     email VARCHAR(50), 
     constraint pk_member_email primary key (email)
);

By the way：

添加主键约束———— ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
撤销主键约束———— ALTER TABLE 表名 DROP CONSTRAINT 约束名;

注意：由于一张表中只能有一个主键，所以在新设主键之前，要先将原主键撤销。

<2>、为已创建的表 “member” 中的 “mid” 列添加主键约束：

--先删
alter table member drop constraint pk_member_email;
--后建
alter table member add constraint pk_member_mid primary key (mid);

<3>、撤销 “mid” 列中的主键约束：

alter table member drop constraint pk_member_mid;

<4>、插入数据：

--正确
INSERT ALL
INTO member VALUES(1,'悟空','[email protected]')
INTO member VALUES(1,'悟空','[email protected]')
SELECT * FROM DUAL;
--错误
INSERT ALL
INTO member VALUES(1,'悟空','[email protected]')
INTO member VALUES(2,'佛祖','[email protected]')
SELECT * FROM DUAL;
--错误信息：违反唯一约束条件(C##SCOTT.PK_MEMBER_EMAIL)

4、检查约束（CHECK）

检查约束指的是对数据增加的条件进行过滤，表中的每数据都必须满足指定的过滤条件。在进行数据更新操作时，如果满足检查约束所设置的条件，则数据可以成功更新；如果不满足，则不能更新。

实例：

<1>、在创建 “member” 表时，创建 age 字段（年龄范围在0-200岁）和 sex 字段（只能是男或女）：

CREATE TABLE member 
(
mid NUMBER,
name VARCHAR(20) NOT NULL,
email VARCHAR(20),
age NUMBER,
sex char(2),
constraint ck_member_age CHECK(age between 0 and 200), 
constraint ck_member_sex check(sex in ('男','女'))
);

By the way：

添加检查约束———— ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (字段名 + 约束条件);
撤销检查约束———— ALTER TABLE 表名 DROP CONSTRAINT 约束名;

<2>、为表中的 “mid” 列添加检查约束，让其范围是10101-99999：

alter table member2 add constraint ck_member_mid check(mid between 10101 and 99999);

<3>、撤销 “mid” 列的检查约束：

alter table member2 drop constraint ck_member_mid;

<4>、插入数据：

--正确
insert into member2 values(20202,99,'女');

--错误
(1)
insert into member2 values(334,99,'男');
--错误信息：违反检查约束条件 (C##SCOTT.CK_MEMBER_MID)
(2)
insert into member2 values(20202,999,'男');
--错误信息：违反检查约束条件 (C##SCOTT.CK_MEMBER_AGE)
(3)
insert into member2 values(20202,99,'人');
--错误信息：违反检查约束条件 (C##SCOTT.CK_MEMBER_SEX)

5、外键约束（foreign key）

前面四种约束都是在一张表中设置的，如果现在要完成两张关系表（父表-子表）的约束设置，则可以通过主-外键约束（也可以简称为外键约束）完成。

1）实例：

<1>、创建成员表 (父表) 和建议表 (子表) 的同时，分别将成员编号 (mid) ，建议编号 (adid) 设为主键，再在子表中将成员编号 (mid) 设为外键。

CREATE TABLE member 
(
     mid NUMBER,
     name VARCHAR(100)  NOT NULL,
     CONSTRAINT pk_member_mid PRIMARY KEY(mid)
);

CREATE TABLE advice
(
    adid NUMBER,
    content CLOB NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_advice_adid PRIMARY KEY(adid),
    CONSTRAINT fk_advice_mid FOREIGN KEY(mid）REFERENCES member(mid)
);

<2>、已建好表中设立外键

create table member
(
	mid number,
	name varchar(20) not null,
	CONSTRAINT pk_member_mid PRIMARY KEY(mid)
);

create table advice
(
	adid NUMBER,
    content CLOB NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_advice_adid PRIMARY KEY(adid)
);

将 mid 设为外键约束

ALTER TABLE member  
add CONSTRAINT fk_advice_mid 
FOREIGN KEY (mid) REFERENCES member(mid);

<3>、插入数据：

--正确
INSERT INTO member(mid,name) VALUES(1,'悟空');
INSERT INTO member(mid,name) VALUES(2,'哪吒');

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,'宝塔，震慑',2);

--错误
INSERT INTO advice(adid,content,mid) VALUES(4,'路上妖精，有的如此厉害',99);
--错误信息：违反完整约束条件 (C##SCOTT.FK_ADVICE_MID) - 未找到父项关键字

2）级联操作：

由于子表的所有数据都要和父表的数据对应，所以在删除父表数据时需要先将子表中对应的数据删除干净，否则将无法删除

示例1：删除member表中编号为“1”的数据（mid=1），此时没有删除子表（advice）数据

DELETE FROM member WHERE mid=1 ;
--错误信息：违反完整约束条件 (ZXD.FK_MID) - 已找到子记录

member中的mid数据与advice有关联关系，所以只能先删除advice中的数据，才能删除member的数据；先删除子表（advice）中mid=1的数据，之后再删除父表（member）中mid=1的数据。

--先删
DELETE FROM advice WHERE mid=1 ;
--后删
DELETE FROM member WHERE mid=1 ;

这样的删除较为复杂，可以通过级联操作简化操作。

By the way：

on delete cascade：当主表数据删除时，对应的子表数据同时删除；
on delete set null：当主表数据删除时，对应的子表数据设置为 null；

<1>、级联删除

级联删除是指在建立外键约束时通过on delete cascade子句设置，这样在删除父表数据时，由父表数据关联的所有子表数据都会被同时删除。

示例1：修改表创建语法，增加级联删除，同时配置测试数据

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,'李兴华') ; 
INSERT INTO member (mid,name) VALUES (2,'董鸣楠') ;
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制，设置总裁邮箱',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动，让员工更加有归属感',2) ;

此时删除member表中编号为1的成员信息，可以触发级联操作，完成删除动作；

DELETE FROM member WHERE mid=1 ;

<2>、级联设置null

该级联操作在删除父表时，子表的数据不会被删除，并将与父表关联的字段设置为null。

示例2：修改表的创建语句，增加ON DELETE SET NULL子句

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,'hey') ; 
INSERT INTO member (mid,name) VALUES (2,'yan') ;
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制，设置总裁邮箱',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动，让员工更加有归属感',2) ;

删除member表中mid为1的记录

DELETE FROM member WHERE mid=1 ;

3）删除父表

如果想删除父表，只能先删除子表，在删除父表

示例1：先删除子表（advice），再删除父表（member）

DROP TABLE advice PURGE ;
DROP TABLE member PURGE ;

强制删除父表：
这种删除方式会直接干掉父表，但是子表，以及与父表关联的字段数据都会得到保留；
如果使用了此种方式，就不能再直接使用purge选项了；

DROP TABLE member CASCADE CONSTRAINT ;

使用了上面的sql删除member表后，查询advice表。可以发现mid依然有父表的值，但它们与父表毫无关系了；

6、查看约束信息

SELECT * FROM DBA_CONSTRAINTS;

SELECT * FROM ALL_CONSTRAINTS;

SELECT * FROM USER_CONSTRAINTS;

SELECT * FROM DBA_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;

SELECT * FROM USER_CONS_COLUMNS;

over.

Oracle完整性约束

一、完整性约束简介

二、Oracle 常用的五大约束

CentOS 7.4下安装Oracle 11.2.0.4数据库的方法

Linux 7.4上安装配置Oracle 11.2.0.4图文教程

PHP+Oracle本地开发环境搭建方法详解

Oracle 经典面试题

Shell实现的Oracle启动脚本分享

Ubuntu 12.10下安装 Oracle Java分享教程

Oracle数据库体系结构实例讲解

oracle客户端ssh跳转实例教程

ORACLE官方SQL语言参考之关于SQL函数的实例讲解

oracle数据泵导入表基础：impdp+ftp的导入步骤