Oracle 10G中关于约束在表和列中使用详解

A:Check约束

1 --创建表

 2 create table order_status

 3 (

 4   id integer constraint order_status_pk primary key ,

 5   status varchar(120) ,

 6   last_modified date default sysdate

 7 );

 8 --添加Check约束

 9 alter table order_status 

10 add constraint order_status_ck check (status in('BeiJing','ShangHai','TaiWan'));

11 

12 alter table order_status

13 add constraint order_status_id_ck check(id>10);

14 --测试数据

15 insert into order_status(id,status)

16 values(132,'ShangHai'); 

注意:

(1)在创建Check约束时,表中所有的行必须满足添加约束条件, 如果在添加约束已经存在数据 条件不满足会提示一个ORA-02293错误:截图如下:

<a target="_blank" href="http://blog.51cto.com/attachment/201201/112706327.gif"></a>

一般处理在添加约束前最好清空原表中数据,上面提示错误有一个例外:在添加约束将其禁用. 这就涉及到"启用约束"和"禁用约束"操作.后面详解.还可以指定Enable Novalidate选项.从而使约束只适用于新添加数据.而不必管约束添加前数据.在插入数据时如果数据不符Check约束会提示一个ORA-02290错误.修改插入数据即可.

B:Not Null约束

--添加上述创建表 not null约束

 alter table order_status

 modify status constraint  order_status_newref not null;

 alert table order_status

 modify last_modified not null;

 格式:注意使用Modify 而非以前使用的Add Constraint

 格式如下:

 alert table [表名] modify [表中列名] constraint [约束唯一标示] not null; 

A1:在为Last_modified添加约束时 并没有使用约束命名 是允许的 系统会自动分配一个名称来标识. 但建议使用手动使用一个有意义的命名 出现错误容易判断出处

C:Foreign Key外键约束.

1 --创建一个外键约束测试表

 2 create global temporary table test_orderstatus

 4   id integer constraint order_statustest_pk primary key,

 5   status_id number(15),

 6   status varchar(120),

 7   last_modifieddate date default sysdate

 8 );

 9 

10 --添加一列 先删除 后添加

11 alter table test_orderstatus

12 drop  column status_id;

13 

14 alter table test_orderstatus

15 add constraint order_status_modify_fk status_id reference order_status(id);

对Foreign key 使用On Delete Cascade子句.即指定当父表中删除一行记录时 回自动删除子表中于其外键关联的行记录.

1 --附带自动级联更新

2 alter table test_orderstatus

3 drop  column status_id;

4 

5 alter table test_orderstatus

6 add constraint order_status_modify_fk status_id reference order_status(id) on delete cascade; 

当在删除了父表一条记录时可以不删除子表记录. 全部设置字表中于外键关联所有记录为Null 则使用On Delete Cascade Null子句. 同样加在 外键约束后位置.当删除一条父表记录时.字表所有通过当前外键同父表关联的数据都设置为空值.

D:Unique约束

1 --添加Unique约束

2 alter table order_status

3 add constraint order_status_uq unique(status); 

E:删除约束(Delete References)

1 --删除刚刚设置的Unique约束

3 drop constraint order_status_uq;【约束唯一标识】 

当需要手动编码删除约束时. 找到约束唯一命名是关键. 所以推荐建立使用有意义的约束命名.容易识别

F:禁用约束(Disable References)

--在创建时就禁用约束

alter table order_status

add constraint order_status_uq unique(status) disable;

添加约束默认是在创建后自动启用的. 创建时可以直接禁用 直接尾部加一个Disable子句 如果在创建后 在来禁用约束则使用Alter table .....Disable Constraint子句

1 --使用中禁用约束

2 alert table order_status

3 disable constraint order_status_uq;【禁用上面创建唯一约束】 

E:启用约束(ReStart References)

1 --启用已经创建约束

3 enable constraint order_status_uq;

约束默认在创建后自动启用.要启用约束注意当前表中所有数据必须满足约束条件. 不然会包ORA-02293错误.当然有时为了需要可以只对新插入数据进行约束.原始依然存在,使用Alter Table.......Enable Novalidate. 默认是Enable Constraint.

--只对新插入的数据才执行当前约束

alert table order_status

enable novalidate constraint order_status_uq; 

shangmian我们在用到上面第一个Check约束时也谈到这个问题.当我们建立Check约束,因为表中在建立约束前原来还存在数据 其中这些数据有些不符合当前约束的 但是我们不想删掉. 那么使用Enable Novalidate子句就是一个很好选择, 它子对新插入的数据其约束. 原始数据进行保留.

G:延迟约束(Deferred Constraint)

延迟约束是在事务被提交时强制执行的约束.添加约束时可以通过Deferrable子句来指定约束为延迟约束. 约束一但创建以后, 就不能修改为Deferrable延迟约束.唯一办法: 删除该约束,只能在创建指定为延迟约束即可.

1 --当前来指定上述创建的唯一约束为延迟约束 先删除已经创建唯一约束 

2 --在创建中再次指定为延迟

3 alter table order_status

4 drop constraint order_status_uq

5 

6 alert table order_status

7 add constraint order_status_uq unique (status) deferrable initially deferred; 

注意上面再添加约束时. 可以将其标识为Initially Immediate 或Initially Deferred.

其中Initially Immediate意思是每次向表中添加数据,修改数据或是从表中删除数据时.都要检查这个约束.(这与约束默认行为相同). 而Initially Deferred.意思是只有事务被提交时才检查这个约束. 上述我们指定的Deferred . 即在只有事务提交时才检查该约束.

H:获取关于约束系统信息

可以通过查询User_Constraints表获得当前所有关于约束的系统信息.下面关于约束User_Constraints表常用信息字段说明:

Owner——约束所有者

Constraint_name——约束名称

Constraint_Type——约束类型:C:代表Check或Not Null约束.  P:主键约束.   R:外键约束.  U:唯一约束.  V:Check option约束.  O:Readonly 只读约束

Table——name——约束定义针对表名

Status_约束的状态 Enable或Disable 可用或不可用

Deferrable:是否为延迟约束 值为:Deferrable或Not Deferred.

Deferred:是否采用延迟 值为:IMMEDIate或Deferred.

1 --查看全部的关于order_status'表约束信息

2 select * from user_constraints where table_name='order_status' 

shangshu是关于表的约束. 对于表中特定的列只需查询User_cons_columns表即可 不在赘述.

本文转自chenkaiunion 51CTO博客，原文链接:http://blog.51cto.com/chenkai/765118