删除冗余约束

有人问道：我有系统表中存在大量的冗余的SYS开头的约束，不能确定它们怎么来的，通常这种情部是什么原因引起的？
SYS_C0010002    AIISPRODUCT_ID
SYS_C0010011    AIISPRODUCT_ID
SYS_C0010873    AIISPRODUCT_ID
SYS_C0010882    AIISPRODUCT_ID
............
............
............
SYS_C0010007    CUSTTYPE_ID
SYS_C0010016    CUSTTYPE_ID
SYS_C0010878    CUSTTYPE_ID
SYS_C0010887    CUSTTYPE_ID
SYS_C0012329    CUSTTYPE_ID
SYS_C0012338    CUSTTYPE_ID
............

--tom先生回答如下
这种情况大概是exp/imp引起的　　例子如下
SQL> create table test (x int check(x>5),y int check(y>10),z int not  null,a int
 unique,b int references test,c int primary key);

表已创建。

SQL> column search_condition  for a20
SQL> select  constraint_name,constraint_type,search_condition from user_constraints where table_name ='TEST';

CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------
SYS_C005267                    C "Z" IS NOT NULL
SYS_C005268                    C x>5
SYS_C005269                    C y>10
SYS_C005270                    P
SYS_C005271                    U
SYS_C005272                    R

--exp 导出
SQL>host exp userid=uname/pwd tables=test
Export: Release 10.2.0.1.0 - Production on 星期五 12月 2 10:10:39 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                            TEST导出了           0 行
成功终止导出, 没有出现警告。

--imp 导入
SQL>host imp userid=xep/xep tables=test ignore=y

Import: Release 10.2.0.1.0 - Production on 星期五 12月 2 10:10:54 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 XEP 的对象导入到 XEP
. 正在将 XEP 的对象导入到 XEP
. . 正在导入表                          "TEST"导入了           0 行
成功终止导入, 没有出现警告。


SQL> column search_condition  for a20
SQL> select  constraint_name,constraint_type,search_condition from user_constraints where table_name ='TEST';

CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------
SYS_C005267                    C "Z" IS NOT NULL
SYS_C005268                    C x>5
SYS_C005269                    C y>10
SYS_C005270                    P
SYS_C005271                    U
SYS_C005272                    R
SYS_C005273                    C x>5
SYS_C005274                    C y>10

这时候可以看到　多了两个check约束，其余的约束未变，再做一例子

SQL> drop table test purge;

表已删除。

SQL> create table test( x int check ( x > 5 ), y int constraint my_rule check ( y > 10 ), z int not null , a int unique,b int references test,  c int primary key);

表已创建。

SQL> select  constraint_name,constraint_type,search_condition from user_constraints where table_name ='TEST';

CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------
SYS_C005285                    C "Z" IS NOT NULL
SYS_C005286                    C  x > 5
MY_RULE                        C  y > 10
SYS_C005288                    P
SYS_C005289                    U
SYS_C005290                    R

--同样再执行导出　
--再导入
经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 XEP 的对象导入到 XEP
. 正在将 XEP 的对象导入到 XEP
. . 正在导入表                          "TEST"导入了           0 行
IMP-00017: 由于 ORACLE 错误 2264, 以下语句失败:
 "ALTER TABLE "TEST" ADD CONSTRAINT "MY_RULE" CHECK ( y > 10 ) ENABLE NOVALID"
 "ATE"
IMP-00003: 遇到 ORACLE 错误 2264
ORA-02264: 名称已被一现有约束条件占用
即将启用约束条件...
成功终止导入, 但出现警告。

--这样就发现了　如果建表时候未未给check约束命名与已命名约束的区别


------------------------------------------------------------------------------------------------
如何删除这些自动生成的约束呢？

SQL> create table t1 as select table_name,constraint_name cn ,to_lob(search_condition) sc from user_constraints where constraint_type='C';

表已创建。


SQL> select table_name,cn ,dbms_lob.substr(sc,4000,1) search_condition,row_number() over(partition by table_name,dbms_lob.substr(sc,4000,1) order by cn) rn fromt1


TABLE_NAME                     CN                                   SEARCH_CONDITION             RN
------------------------------ ------------------------------       -------------------- ----------

TEST                           SYS_C005286                           x > 5                        1

TEST                           SYS_C005291                           x > 5                        2

TEST                           MY_RULE                              　 y > 10                       1

TEST                           SYS_C005285                          "Z" IS NOT NULL               1

已选择6行。

SQL> select 'alter table '||table_name||' drop constraint '|| cn || ';'  from (select table_name,cn,dbms_lob.substr(sc,4000,1) search_condition,row_number() over(partition by table_name,dbms_lob.substr(sc,4000,1) order by cn ) rn from t1 ) where rn >1;

'ALTERTABLE'||TABLE_NAME||'DROPCONSTRAINT'||CN||';'
--------------------------------------------------------------------------------

alter table TEST drop constraint SYS_C005291;