Oracle 约束（constraint）

共计 6794 个字符，预计需要花费 17 分钟才能阅读完成。

(一)约束的概念

在 Oracle 中，可以通过设置约束来防止无效数据进入表中。Oracle 一共有 5 种约束：

主键约束（primary key）
外键约束（foreign key）
唯一性约束（unique）
非空约束（not null）
检查约束（check）

（1）主键约束

– 主键约束可以定义在一列或多列上，值具有唯一性、非空性；

– 在一个表上只能定义一个主键约束；

–Oracle 会自定在主键约束的列上创建 唯一性索引，可以指定唯一性索引的位置及存储参数。

（2）外键约束

– 外键约束列的取值来源于参照表（父表）的参照列的值，或者空值；

– 定义外键约束的列只能参照父表的主键约束列和唯一性约束列；

– 父表与子表必须在同一个数据库中。

（3）唯一性约束

– 可定义在一列或多列上，列的取值必须唯一；

– 如果在某些列上定义了唯一性约束，而没有定义非空约束，那么在这些列上可以出现多个空值；

–Oracle 会自定在主键约束的列上创建 唯一性索引，可以指定唯一性索引的位置及存储参数。

（4）非空约束

– 只能定义在列上；

– 在同一个表中可定义多个非空约束。

（5）检查约束

– 检查约束用来限制列的取值范围，其表达式必须引用相应列，表达式的计算结果是一个布尔值；

– 一个列可以定义多个检查约束

（二）列级约束与表级约束

列级约束是对 某一个特定列 的约束，包含在列的定义中，直接跟在其它定义之后，不需要指出列名。其语法为:

column_definition [CONSTRAINT constraint_name] constraint_type ...

表级约束的定义与列的定义相互独立，通常用于多列组合的约束，定义表级约束要指出约束的名称。其语法为：

[CONSTRAINT constraint_name]
constraint_type(column1,[column2...]);

需要注意的是，除了非空约束只能定义列级约束外，其他的均可定义列级 / 表级约束。

（三）约束的状态

根据在数据插入、更新时是否对数据进行约束检查，约束状态分为激活状态（ENABLE）和禁用状态（DISABLE）；

根据是否对表中已有数据进行约束检查，约束状态分为验证状态（VALIDATE）和非验证状态（NOVALIDATE）；

组合之后，一共有四种约束状态：

约束状态	定义	特点
激活验证状态（ENABLE VALIDATE）	激活约束且约束检查已有数据	对表中已有数据进行检查，对后续插入、更新的数据也会进行检查
激活非验证状态（ENABLE NOVALIDATE）	激活约束但是不检查表中已存在的数据	不检查目前表中已经存在的数据，但会检查后续插入、更新的数据
禁用验证状态（DISABLE VALIDATE）	不激活约束但是会去检查表中已经存在的数据	Oracle 不允许进行任何 DML（insert/update/delete）操作，因为操作无法得到约束的检查。表处于只读模式
禁用非验证状态（DISABLE NOVALIDATE）	不激活验证且不检查表中已有的数据	不会对表中数据进行约束检查，也不会对新插入的数据进行约束检查

（四）创建约束

（1）创建列级约束

创建列级约束的语法为：

INITIALLYCONSTRAINT constraint_name                           --约束名
[                                                             -- 选择 5 中约束类型
 [NOT] NULL             |
 [UNIQUE]               | 
 [PRIMARY KEY]          |
 [CHECK (condition)]    |
 [REFERENCES [schema.]object[(column)]  [ON DELETE CASCADE | SET NULL]] 
]
[                                                            -- 是否延迟约束检查
 [NOT DEFERRABLE]       |
 [DEFERRABLE [INITIALLY IMMEDIATE | DEFERRED]]
]
[ENABLE | DISABLE]                                            --定义约束检查的类型
[VALIDATE | NOVALIDATE ]  
[USING INDEX index_clause]                                    --约束上索引的定义（主键约束与唯一性约束）
[EXCEPTIONS INTO [schema.]table]                              --违反约束的数据的存储方式

参数说明：

–cinstraint_name：约束名，如果没有为约束命名，oracle 将自动为约束命名，命名样式为 SYS_Cn，n 是数据库对象的唯一编号；

–ON DELETE CASCADE：定义级联删除的外键约束；

–ON DELETE SET NULL：定义置空删除的晚间约束；

–NOT DEFERRABLE：约束不可延迟；

–DEFERRABLE：约束可以延迟；

–INITIALLY IMMEDIATE：可延迟约束的立即检查；

–DEFERRED：可延迟约束的延迟检查；

–USING INDEX index_clause：创建唯一性索引的参数设置（存在与主键约束、唯一性约束）；

–EXCEPTIONS INTO：将违反约束的记录保存到表中。

（2）定义表级约束

列级约束是定义在列上的，如果需要在多个列上定义约束，则需采用表级约束，表级约束语法如下：

.INITIALLYCONSTRAINT constraint_name                           --约束名
[                                                             -- 选择 4 种约束类型，非空约束只能定义在列上
 [UNIQUE(column1[,column2…])]               | 
 [PRIMARY KEY(column1[,column2…])]          |
 [CHECK (condition)]                         |
 [FOREIGN KEY (column1[,column2…])                                                           -- 外键约束需要指出要约束的列，与列级约束有较大区别

REFERENCES [schema.]object[(column1[,column2…])] [ON DELETE CASCADE | SET NULL]]        
]
[                                                             -- 是否延迟约束检查
 [NOT DEFERRABLE]                           |
 [DEFERRABLE [INITIALLY IMMEDIATE | DEFERRED]]
]
[ENABLE | DISABLE]                                            --定义约束检查的类型
[VALIDATE | NOVALIDATE ]  
[USING INDEX index_clause]                                    --约束上索引的定义（主键约束与唯一性约束）
[EXCEPTIONS INTO [schema.]table]                              --违反约束的数据的存储方式

（五）添加、修改、重命名、删除约束

（1）添加约束

语法为：

ALTER TABLE table_name 
ADD [CONSTRAINT constraint_name] constraint_type(column1[,column2...])
[constraint_parameters];

例子 1. 添加约束

--创建表 books
create table books
(id       number(6),
  title    varchar2(20),
  isbn     number(20),
  auther   varchar(10),   
  price    number(6,2),
  discribe varchar2(1000),
  pid      number
);
Table created

--创建表 publish（用来辅助 books 创建外键约束）
create table publish
(pid     number                    primary key,
  name    varchar2(20)
);
Table created

--添加主键约束
SQL> alter table books add constraint books_pk primary key (id);
 
Table altered
 
--添加外键约束
--需要注意，参照的父表的列需要是主键约束列或唯一性约束列

-- 主要注意，外键约束的 references 带‘s’
SQL> alter table books add constraint books_fk foreign key (pid) references publish(pid);
 
Table altered
 
--创建唯一性约束
SQL> alter table books add constraint books_unique unique (title);
 
Table altered
 
--创建检查约束 
SQL> alter table books add constraint books_check check (price > 0);
 
Table altered
 
--需要特别注意，非空约束只能使用 modify 来创建 / 删除，不能使用 add 方式
--1. 创建非空约束
SQL> alter table books modify auther not null;
 
Table altered
--2. 删除非空约束
SQL> alter table books modify auther null;
 
Table altered

（2）修改约束

可以使用 ALTER TABLE … MODIFY 修改约束参数，语法为：

ALTER TABLE table_name 
MODIFY [CONSTRAINT constraint_name]  |
[PRIMARY KEY] | [UNIQUE(column1[,column2,...])]  --主键约束和唯一性约束可以通过约束类型来修改
[constraint_parameters]

例子 2. 修改约束

--修改检查约束，将其置为不检查表中已有数据
SQL> alter table books modify constraint books_check novalidate;
 
Table altered

--使主键约束不可用 
SQL> alter table books modify primary key disable;
 
Table altered

（3）重命名约束

可以使用 ALTER TABLE … RENAME…来重命名约束，语法为：

ALTER TABLE table_name RENAME CONSTRAINT old_constrain_name TO new_constraint_name;

（4）删除约束

可以使用 ALTER TABLE … DROP …来删除约束，语法为：

ALTER TABLE table_name 
DROP [CONSTRAINT constraint_name]    |
[PRIMARY KEY]   |    [UNIQUE (column1 [,column2,...])]
[CASCADE]  [KEEP | DROP INDEX]

对于删除约束，需要注意：

1. 可以通过约束名称来删除约束，对于主键约束和唯一性约束，也可以指定约束的类型来删除约束；

2. 如果要删除的主键约束或唯一性约束已经被子表中的外键约束引用，需要加 CASCADE 关键字，删除子表中的外键约束；

3. 在删除主键约束 / 唯一性约束时，如果要保留主键约束 / 唯一性约束创建时生成的唯一性索引，则需要使用 KEEP 关键字。

Oracle 约束（constraint）

图、books 与 publish 的关系

例子 3. 删除主键约束

-- 根据上图，我们发现 books 的外键引用了 publish 的主键，如果直接删除，会报错，只有加了 cascade 后才能删除主键约束

-- 删除主键约束后才去看 books 表，对应的外键约束也被删除了

SQL> alter table publish drop primary key;
 
alter table publish drop primary key
 
ORA-02273: 此唯一 / 主键已被某些外键引用
 
SQL> alter table publish drop primary key cascade;
 
Table altered

（六）关于外键约束的数据删除方式

假如两个表之间存在外键约束的关系，在父表进行数据删除时，子表的关联数据如何处理？Oracle 提供了 3 种方式。

(1)受限删除【默认】，如果子表中有与父表关联的记录存在，则不能删除数据；

例子 4. 受限删除无法删除已经关联的数据

create table table_a      --父表
(id           number        primary key,
  name         varchar(20)
);

create table table_b1     --子表
(id           number         references table_a(id),         -- 默认是受限删除
  country      varchar2(30)    
)

SQL> insert into table_a values(1,'lijiaman');
 
1 row inserted
 
SQL> insert into table_b1 values(1,'test');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> delete table_a where id = 1;         --子表有关联数据，导致父表数据也无法删除
 
delete table_a where id = 1
 
ORA-02292: 违反完整约束条件 (LIJIAMAN.SYS_C0013519) - 已找到子记录
 
SQL> insert into table_a values(2,'fg');
 
1 row inserted
 
SQL> delete table_a where id = 2;        --子表无关联数据，父表数据可以删除
 
1 row deleted
 
SQL> commmit;

（2）级联删除，删除父表数据的同时也会将外键关联的子表的记录删除；

例子 5. 级联删除外键约束会删除子表关联数据

SQL> drop table table_b1;        --删除 table_b1
 
Table dropped

create table table_b2             --子表
(id           number         references table_a(id) on delete cascade,                          --级联删除外键约束
  country      varchar2(30)    
);
SQL> insert into table_b2 values(1,'ch');
 
1 row inserted

SQL> select * from table_a ;                                       --两个表各有 1 行关联数据
 
        ID NAME
---------- --------------------
         1 lijiaman
 
SQL> select * from table_b2;
 
        ID COUNTRY
---------- ------------------------------
         1 ch
 
SQL> delete table_a where id = 1;                         --删除父表数据，子表关联数据也随之删除
 
1 row deleted
 
SQL> commit;
 
Commit complete
 
SQL> select * from table_a;
 
        ID NAME
---------- --------------------
 
SQL> select * from table_b2;
 
        ID COUNTRY
---------- ------------------------------

（3）置空删除，父表数据删除后，子表中关联记录的外键约束的列值置为空；

例子 6. 置空删除外键约束会将子表关联记录的外键列置为空值

create table table_b3          --子表
(id           number         references table_a(id) on delete set null,  --置空删除外键约束
  country      varchar2(30)    
);

SQL> insert into table_a values(1,'adrci');                --往两个表插入 1 行关联数据
 
1 row inserted
 
SQL> insert into table_b3 values(1,'srvctl');
 
1 row inserted

SQL> commit;
 
Commit complete
 
SQL> select * from table_a;
 
        ID NAME
---------- --------------------
         1 adrci
 
SQL> select * from table_b3;
 
        ID COUNTRY
---------- ------------------------------
         1 srvctl
 
SQL> delete table_a where id = 1;                     --删除 id= 1 的数据，父表整条记录被删除，子表仅置空了关联数据的外键列值
 
1 row deleted
 
SQL> select * from table_a;
 
        ID NAME
---------- --------------------
 
SQL> select * from table_b3;
 
        ID COUNTRY
---------- ------------------------------
           srvctl

（七）与约束相关的数据字典

视图名称	说明
DBA_CONSTRAINTS ALL_CONSTRAINTS USER_CONSTRAINTS	包含数据库中所有约束的定义信息；包含当前用户可以看到的所有约束的定义信息；包含当前用户拥有的约束的定义信息；
DBA_CONS_COLUMNS ALL_CONS_COLUMNS USER_CONS_COLUMNS	记录了约束列信息，DBA_、ALL_、USER_的用法与前面相同