Oracle Constraints

--Constraints

--约束详细信息查看
select constraint_name, --约束名称  
       constraint_type, --约束类型  
       table_name, --约束所在的表  
       search_condition, --约束表达式  
       status --是否启用  
  from user_constraints --[all_constraints|dba_constraints]  
 where constraint_name = 'CHECK_TB_SUPPLIER_ID';

--通过约束名字查询约束
set lines 300
col owner for a15
col TABLE_NAME for a20
col CONSTRAINT_NAME for a25
col SEARCH_CONDITION for a20
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION,STATUS from dba_constraints 
where owner='ITSM_XHRS' 
and CONSTRAINT_NAME='FK_VAR_LOB';

--通过index名字查询约束类型
set lines 300
col owner for a8
col TABLE_NAME for a20
col CONSTRAINT_NAME for a25
col CONSTRAINT_TYPE for a15
col INDEX_NAME for a20
col SEARCH_CONDITION for a20
select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME,TABLE_NAME,STATUS from dba_constraints 
where CONSTRAINT_TYPE = 'P'
--and owner = ''
and INDEX_NAME in
(
'PK_LAMANOEUVRE',
'IDX_ES_DOC_MAIN_BTYPE'
);

--查询表主键列名
select *
  from dba_cons_columns
 where constraint_name =
       (select constraint_name
          from dba_constraints
         where constraint_type = 'P'
           and table_name in ('BST_FAVORITE'));

--是否存在主外键约束
col owner for a10
col CHILD_TABLE for a15
col FATHER_TABLE for a15
col CONSTRAINT_NAME for a20
--子表
select 'Refer to' as type,
       p.owner,
       c.table_name child_table,
       p.table_name father_table,
       c.constraint_name,
       c.status,
       c.deferrable,
       c.deferred
  from dba_constraints c, dba_constraints p
where c.owner = 'LIS'
   and c.table_name = 'LCPOL'
   and c.constraint_type = 'R'
   and c.r_owner = p.owner
   and c.r_constraint_name = p.constraint_name
union
--父表
select 'Be refered' as type,
       c.owner,
       c.table_name child_table,
       p.table_name father_table,
       c.constraint_name,
       c.status,
       c.deferrable,
       c.deferred
  from dba_constraints c, dba_constraints p
where p.owner = 'LIS'
   and p.table_name = 'LCPOL'
   and c.constraint_type = 'R'
   and c.r_owner = p.owner
   and c.r_constraint_name = p.constraint_name;

--查看约束字段
select a.constraint_name,a.constraint_type,b.column_name 
from dba_constraints a,dba_cons_columns b
where a.table_name=b.table_name;

--删除所有外键约束
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP drop constraint FK_TEST;

--禁用所有外键约束
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP disable constraint FK_TEST;

--启用所有外键约束
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'   
--alter table EMP enable constraint FK_TEST;