一、约束的种类

not null：非空约束

unique：唯一约束，需要索引支持，没有索引会自动创建，但列允许多个NULL，NULL不在其索引中

primary key：主键约束，实质是unique + not null，需要索引支持，没有索引会自动创建

foreign key：外键约束，定义在子表上，对应父表的主键，允许多个NULL

check：规则检查约束，规则是值为true或false的表达式，可以是字面值、其他列、部分函数，但不能是sysdate等函数，不能用子查询

约束类型

二、约束的定义

创建表时定义

create table dept(

deptno number(2,0)

constraint pk_dept_deptno primary key

constraint ck_dept_deptno check(deptno between 10 and 90),

Dname varchar2(20)

constraint nn_dept_dname not null);

create table emp(

empno number(4,0)

constraint pk_emp_empno primary key,

ename varchar2(20)

constraint nn_emp_ename not null,

mgr number(4,0)

constraint fk_emp_mgr references emp(empno),

dob date,

hiredate date,

deptno number(2,0)

constraint fk_emp_deptno references dept(deptno) on delete set null,

email varchar2(30)

constraint uk_emp_email unique,

constraint ck_emp_hiredate check(hiredate>=dob+365*16),

constraint ck_emp_email check((instr(email,'@')>0) and (instr(email,'.')>0)));

创建表后使用alter table命令添加约束
添加主键约束
alter table emp add constraint pk_emp primary key(empno);
添加外键约束
alter table sales add constraint fk_prod_id foreign key(prod_id) references products(prod_id);
添加非空约束
alter table customers modify customer_name constraint nn_cust_name not null;
如果直接使用系统默认定义的约束名，则可简化为
alter table customers modify customer_name not null;
添加唯一约束
alter table c_material_t add constraint uk_material_no unique(material_no);
添加check约束
alter table cmes.c_material_t add constraint ck_validaterule check(validaterule not like '%*.%');

具有主外键约束关系的父子表，删除主表行将引发错误，处理办法：

1、外键约束定义为on delete cascade，则父表行删除时，子表匹配行自动删除

2、外键约束定义为on delete set null，则父表行删除时，子表匹配行外键列自动置空

unique和primary key约束需要索引，如果不存在会自动创建，因此最好在定义约束之前就先创建好索引，便于主动维护。

利用子查询建表时，非空约束可以被带过来，其它约束不行，以下举例说明：

创建四张表

create table customers(cust_id number, country_id number);

create table products(prod_id number(6), prod_name varchar2(50));

create table times(time_id date, day_name varchar2(9));

create table sales(prod_id number, cust_id number, time_id date, channel_id number);

添加约束

alter table customers add constraint pk_cust_id primary key(cust_id);

alter table customers modify country_id constraint nn_country_id not null;

alter table products add constraint pk_prod_id primary key(prod_id);

alter table products modify prod_name constraint nn_prod_name not null;

alter table times add constraint pk_time_id primary key(time_id);

alter table times modify day_name constraints nn_day_name not null;

alter table sales add constraint fk_prod_id foreign key (prod_id) references products(prod_id);

alter table sales add constraint fk_cust_id foreign key (cust_id) references customers(cust_id);

alter table sales add constraint fk_time_id foreign key (time_id) references times(time_id);

alter table sales modify channel_id constraint nn_channel_id not null;

创建新表

create table new_sales(prod_id, cust_id, order_date default sysdate, channel_id)

as select prod_id, cust_id, time_id, channel_id from sales;

查看新表约束，非空约束系统已定义，其它约束没有

col constraint_name for a20

col table_name for a20

col column_name for a20

col r_constraint_name for a20

select constraint_name, constraint_type, table_name, column_name, r_constraint_name, status from user_constraints natural join user_cons_columns where table_name = 'NEW_SALES';

CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME          R_CONSTRAINT_NAME    STATUS

-------------------- - -------------------- -------------------- -------------------- --------

SYS_C0019014         C NEW_SALES            CHANNEL_ID                                ENABLED

三、查看约束

约束的查询主要通过两个数据字典dba_constraints（user_constriants）、dba_cons_columns（user_cons_columns）。

查看表包含的约束

col owner for a10

col constraint_name for a20

col table_name for a20

col r_constraint_name for a20

select owner, constraint_name, constraint_type, table_name, r_constraint_name, status from dba_constraints where owner = 'SCOTT' and table_name = 'EMP';

OWNER      CONSTRAINT_NAME      CONSTRAINT_TYPE TABLE_NAME           R_CONSTRAINT_NAME    STATUS

---------- -------------------- --------------- -------------------- -------------------- --------

SCOTT      PK_EMP               P               EMP                                       ENABLED

SCOTT      FK_DEPTNO            R               EMP                  PK_DEPT              ENABLED

查看表的约束对应的列

col constraint_name for a20

col table_name for a20

col column_name for a20

col r_constraint_name for a20

select owner, constraint_name, constraint_type, table_name, column_name, r_constraint_name, status from dba_constraints natural join dba_cons_columns where owner = 'SCOTT' and table_name = 'EMP';

OWNER      CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME          R_CONSTRAINT_NAME    STATUS

---------- -------------------- - -------------------- -------------------- -------------------- --------

SCOTT      PK_EMP               P EMP                  EMPNO                                     ENABLED

SCOTT      FK_DEPTNO            R EMP                  DEPTNO               PK_DEPT              ENABLED

四、约束的状态转化和延迟检查

约束的状态有两类

启用状态：启用或禁用

验证状态：验证或不验证

启用状态和验证状态的几种可能的组合

enable validate：无法输入违反约束的行，表中的所有行都符合约束

disable novalidate：可以输入任何数据，表中可能存在不符合约束要求的数据

enable novalidate：表中可以存在不符合约束要求的数据，但现在输入的行必须符合要求

disable validate：可以有该状态，但没有意义，DML操作报错，不能对带有禁用和验证约束条件的表进行插入/更新/删除

约束的状态可以通过修改约束命令来转化

insert时可先禁用约束检查，提高数据插入效率，之后再统一修改

alter table <table_name> modify constraint <constraint_name> disable novalidate;

或者

alter table <table_name> disable novalidate constraint <constraint_name>;

执行insert后启用约束，但不验证

alter table <table_name> modify constraint <constraint_name> enable novalidate;

执行update后开启验证

alter table <table_name> modify constraint <constraint_name> enable validate;

如果是主外键约束的关系，在解除主键约束的同时，可以通过cascade级联解除外键约束，如

alter table emp disable constraint pk_empno cascade;

但如果恢复，则以下只能恢复主键约束，外键约束需要另外手动恢复

alter table emp enable constraint pk_empno;

约束的状态转化是全局的，对所有会话有效。

 

约束的延迟检查

immediate：执行语句时检查，默认，不延迟

set constraint <constraint_name> immediate;

deferred：延迟约束，在事务提交时检查

set constraint <constraint_name> deferred;

切换可延迟约束是针对会话而特有的，不是全局的。

 

要使用延迟的约束，必须用适当的方法创建，指明可延迟

alter table <table_name> add constraint <constraint_name> check(<check_condition>) deferrable initially immediate;

延迟约束通常用于外键，防止进程在更新父子表时因处理顺序不当导致的进程操作失败。

五、约束的维护

更改约束名

alter table rename constraint  to ;

删除约束

alter table drop constraint ;

alter table drop primary key cascade;

通过异常表找到违规数据

建表及其约束，插入异常数据，使用异常表排除异常：

创建test表

create table test(c1 number, c2 char(10));

插入正常数据

insert into test values(1, 'aaa');

insert into test values(2, 'bbb');

为test表添加一个unique约束un_c2在c2字段上

alter table test add constraint un_c2 unique(c2);

插入重复数据

insert into test values(3, 'aaa');

此时报错

ORA-00001: 违反唯一约束条件 (SYS.UN_C2)

临时禁用约束un_c2

alter table test modify constraint un_c2 disable;

插入重复数据

insert into test values(3, 'aaa');

commit;

创建exceptions表

@?\rdbms\admin\utlexcpt.sql

恢复约束检查，异常记录登记到异常表

alter table test enable constraint un_c2 exceptions into exceptions;

此时报错

ORA-02299: 无法验证 (SYS.UN_C2) - 找到重复关键字

查询一下异常表，已经有异常记录

select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT

------------------ ------------------------------ ------------------------------ ------------------------------

AAAS1PAABAAAWDBAAC SYS                            TEST                           UN_C2

AAAS1PAABAAAWDBAAA SYS                            TEST                           UN_C2

找出这些异常记录

select rowid, c1, c2 from test where rowid in (select row_id from exceptions);

ROWID                      C1 C2

------------------ ---------- ----------

AAAS1PAABAAAWDBAAA          1 aaa

AAAS1PAABAAAWDBAAC          3 aaa

更新到正确的数据

update test set c2='ccc' where rowid='AAAS1PAABAAAWDBAAC';

commit;

恢复约束检查

alter table test enable constraint un_c2;

清理异常表

truncate table exceptions;