一、准备知识 约束放置在表中，有以下五种约束: not null 非空约束c 指定的列不允许为空值 unique 唯一约束u 指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的 primary key 主键约束p 唯一的标识出表的每一行,且不允许空值值,一个表只能有一
一、准备知识
约束放置在表中，有以下五种约束:
not null 非空约束c 指定的列不允许为空值
unique 唯一约束u 指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的
primary key 主键约束p 唯一的标识出表的每一行,且不允许空值值,一个表只能有一个主键约束
foreign key 外键约束r 一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列
check 条件约束c 指定该列是否满足某个条件
约束命名规则
如果不指定约束名oracle server 自动按照sys_cn 的格式指定约束名，也可手动指定，
推荐的约束命名是：约束类型_表名_列名。
nn：not null 非空约束，比如nn_emp_sal
uk：unique key 唯一约束
pk：primary key 主键约束
fk：foreign key 外键约束
ck：check 条件约束
外键约束是用来维护从表和主表的引用完整性的，所以外键约束要涉及两个表。
foreign key: 在表级指定子表中的列
references: 标示在父表中的列
on delete cascade: 当父表中的列被删除时，子表中相对应的列也被删除
on delete set null: 子表中相应的列置空
二、外键创建测试
foreign_main为主表
foreign_sub为从表
object_id做为foreign_sub的外键，参考主表foreign_main的object_id值
sql> create table foreign_main as select object_id from all_objects;
table created.
sql> select count(*) from foreign_main;
count(*)
----------
49571
sql> create table foreign_sub as select object_id,object_name from all_objects;
table created.
建议使用主表的主键做外键，，即使不是主表的主键也应该是唯一约束的字段做为外键
sql> alter table foreign_main add constraint pk_fsid primary key(object_id);
table altered.
sql> delete from foreign_sub where object_name = 'foreign_main';
1 row deleted.
sql> commit;
commit complete.
sql> alter table foreign_sub add constraint fr_fssid foreign key(object_id) references foreign_main(object_id);
table altered.
从表插入一条主表object_id中不存在的记录测试
sql> insert into foreign_sub values(1,'ts');
insert into foreign_sub values(1,'ts')
*
error at line 1:
ora-02291: integrity constraint (test.fr_fssid) violated - parent key not found
提示主表数据不存在，从表不能创建主表不存在的object_id以保证完整性
三、级联删除测试
sql> alter table foreign_sub drop constraint fk_fs_oid;
table altered.
sql> alter table foreign_sub add constraint fk_fs_oid foreign key(object_id) references foreign_main(object_id) on delete cascade;
table altered.
cascade下仍然不能单独更新主表外键字段
sql> update foreign_main set object_id=52012 where object_id=52010;
update foreign_main set object_id=52012 where object_id=52010
*
error at line 1:
ora-02292: integrity constraint (test.fk_fs_oid) violated - child record found
cascade模式下可以通过主表删除外键字段数据关联删除从表数据
sql> select * from foreign_sub where object_id=52010;
object_id object_name
---------- ------------------------------
52010 idx_bjname
sql> delete from foreign_main where object_id=52010;
1 row deleted.
sql> commit;
commit complete.
sql> select * from foreign_sub where object_id=52010;
no rows selected
外键相关常用操作及参考文档
建立外键
alter table 表名 add constraint 外键名 foreign key(从表外键字段) references foreign_main(主表外键字段);
drop表外键
alter table 表名 drop constraint 外键名;
通过外键找表
select * from user_constraints where constraint_type='r' and constraint_name=upper('外键名');
通过表找外键
select * from user_constraints where constraint_type='r' and table_name=upper('表名');
查找表的外键(包括名称，引用表的表名和对应的键名，下面是分成多步查询)：
select * from user_constraints c where c.constraint_type = 'r' and c.table_name = 要查询的表
查询引用表的键的列名：
select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
外键约束临时disabled
alter table 表名 disable constraint 外键名;
在sql92标准中定义了几种外键改变后，如何处理子表记录的动作，其中包括：
限制restrict：这种方式不允许对被参考的记录的键值执行更新或删除的操作;置为空set to null：当参考的数据被更新或者删除，那么所有参考它的外键值被置为空;
置为默认值set to default：当参考的数据被更新或者删除，那么所有参考它的外键值被置为一个默认值;
级联cascade：当参考的数据被更新，则参考它的值同样被更新，当参考的数据被删除，则参考它的子表记录也被删除;
不做操作no action：这种方式不允许更新或删除被参考的数据。和限制方式的区别在于，这种方式的检查发生在语句执行之后。oracle默认才会的方式就是这种方式。
col owner for a6
col r_owner for a6
col table_name for a15
select owner, table_name, constraint_name, constraint_type, r_owner, r_constraint_name, delete_rule from user_constraints where table_name in ('foreign_main', 'foreign_sub');
select constraint_name from user_constraints e where e.table_name='ims_column' and owner='wsjd_elms6';
select b.table_name,b.column_name, a.constraint_type, c.table_name from user_constraints a, user_cons_columns b, user_constraints c
where a.constraint_name = b.constraint_name and
a.r_constraint_name = c.constraint_name
and a.r_constraint_name in (select constraint_name from user_constraints e where e.table_name='foreign_main' and owner='scott');
create table foreign_sub as select object_id, object_name from user_objects;
create table foreign_main as select object_id from foreign_sub;
alter table foreign_main add constraint pk_foreign_main_object_id primary key(object_id);
alter table foreign_sub add constraint fr_foreign_sub_object_id foreign key(object_id) references foreign_main(object_id) on delete cascade;
alter table foreign_sub drop constraint fr_foreign_sub_object_id;
alter table foreign_sub disable constraint fr_foreign_sub_object_id;
如在goldengate 没有禁用外键约束会出现以现错误
=============================================
2013-12-26 04:51:25 info ogg-00996 oracle goldengate delivery for oracle, rep_app.prm: replicat rep_app started.
.syntaxhighlighter{padding-top:20px;padding-bottom:20px;}【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！