oracle约束名称规则,为什么需要对约束添加名称？

欢迎批评

http://litterbaby.itpub.net/post/16841/278641

为什么需要对约束添加名称？

在设计Oracle数据表的时候，时常需要对表的列添加一定的约束，添加表的时候，约束可以是命名的，也可以是匿名的，而在匿名的时候，系统会对这个约束自动添加上系统命名的约束名，但是如果不对约束命名的时候，有可能在未来的操作中产生性能影响。下面就这个问题做一个试验：

添加一个表：

SQL> create table t (

2   a int check (a>10),

3   b int constraint b_rule check (b>10),

4   c int not null,

5   d int unique,

6   e int primary key

7  );

Table created

检查表的约束：

SQL> select constraint_name name ,constraint_type type,search_condition from user_constraints where table_name='T';

NAME                           TYPE SEARCH_CONDITION

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

SYS_C004820                    C    "C" IS NOT NULL

SYS_C004821                    C    a>10

B_RULE                         C    b>10 (这里只有这个是自己命名的)

SYS_C004823                    P

SYS_C004824                    U

导出表t

SQL> host exp userid=test/test file=d:test.dmp tables=t;

Export: Release 10.2.0.1.0 - Production on 星期五 4月 6 08:54:32 2007

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 字符集

即将导出指定的表通过常规路径...

. . 正在导出表                               T导出了           0 行

成功终止导出, 没有出现警告。

删除表，然后再导入

SQL> drop table t;

表已删除。

SQL> host imp userid=test/test file=d:\test.dmp full=y;

Import: Release 10.2.0.1.0 - Production on 星期五 4月 6 08:56:26 2007

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 字符集中的导入

. 正在将 TEST 的对象导入到 TEST

. 正在将 TEST 的对象导入到 TEST

. . 正在导入表                             "T"导入了           0 行

即将启用约束条件...

成功终止导入, 没有出现警告。

SQL> select constraint_name name ,constraint_type type,search_condition from user_constraints where table_name='T';

NAME                           T SEARCH_CONDITION

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

SYS_C004825                    C "C" IS NOT NULL

SYS_C004826                    C a>10

B_RULE                         C b>10

SYS_C004828                    P

SYS_C004829                    U

这时候可以发现表的约束，命名的约束没有改变，而由系统自动命名的约束却都发生了改变。

这里的原因是由于由于上一次我们的表被删除，随之约束也被删除，在数据被重新导入到数据库中的时候，系统将对这个表进行重新创建，这时候，由系统自动命名的约束将自动添加新的约束给表，系统在添加约束的时候是自动增加的，所以在上面可以看到约束的名是一个递增的情况。

这里还有一个情况需要说明的是：在B_RULE之前和之后命名的顺序是一个不连续的，这里的原因可能是由于B_RULE可以在系统上分配了一个数字来定义，或者这个定义是使用一个序列来实现的，这个占有了一个序列值，所以产生了现在的情况。

再次将表导入，这次 没有删除表

SQL> host imp userid=test/test file=d:\test.dmp full=y ignore=y;

Import: Release 10.2.0.1.0 - Production on 星期五 4月 6 09:08:30 2007

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 字符集中的导入

. 正在将 TEST 的对象导入到 TEST

. 正在将 TEST 的对象导入到 TEST

. . 正在导入表                             "T"导入了           0 行

IMP-00017: 由于 ORACLE 错误 2264, 以下语句失败:

"ALTER TABLE "T" ADD CONSTRAINT "B_RULE" CHECK (b>10) ENABLE NOVALIDATE"

IMP-00003: 遇到 ORACLE 错误 2264

ORA-02264: 名称已被一现有约束条件占用

即将启用约束条件...

成功终止导入, 但出现警告。

SQL> select constraint_name name ,constraint_type type,search_condition from user_constraints where table_name='T';

NAME                           T SEARCH_CONDITION

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

SYS_C004825                    C "C" IS NOT NULL

SYS_C004826                    C a>10

B_RULE                         C b>10

SYS_C004828                    P

SYS_C004829                    U

SYS_C004830                    C a>10

已选择6行。

这里可以看到由两个约束(a>10),这里为什么哪？

这里的主要原因是由于，再次导入时候，

SQL> host imp userid=test/test file=d:\test.dmp full=y ignore=y;

Import: Release 10.2.0.1.0 - Production on 星期五 4月 6 09:13:42 2007

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 字符集中的导入

. 正在将 TEST 的对象导入到 TEST

. 正在将 TEST 的对象导入到 TEST

. . 正在导入表                             "T"导入了           0 行

IMP-00017: 由于 ORACLE 错误 2264, 以下语句失败:

"ALTER TABLE "T" ADD CONSTRAINT "B_RULE" CHECK (b>10) ENABLE NOVALIDATE"

IMP-00003: 遇到 ORACLE 错误 2264

ORA-02264: 名称已被一现有约束条件占用

即将启用约束条件...

成功终止导入, 但出现警告。

SQL> select constraint_name name ,constraint_type type,search_condition from user_constraints where table_name='T

NAME                           T SEARCH_CONDITION

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

SYS_C004825                    C "C" IS NOT NULL

SYS_C004826                    C a>10

B_RULE                         C b>10

SYS_C004828                    P

SYS_C004829                    U

SYS_C004830                    C a>10

SYS_C004831                    C a>10

已选择7行。

这里我们可以看到在每一次导入的时候，都会有一个约束出现，而且这个约束是相同的，但是，我们命名的约束却没有出现，而是在导入的时候有IMP-00017: 由于 ORACLE 错误 2264, 以下语句失败:

"ALTER TABLE "T" ADD CONSTRAINT "B_RULE" CHECK (b>10) ENABLE NOVALIDATE"

IMP-00003: 遇到 ORACLE 错误 2264

ORA-02264: 名称已被一现有约束条件占用

警告的产生。这是因为由于系统自动命名的时候，名称不会重复，所以系统不会出现上面类似警告的产生。

这样由于我们导入数据次数的增多，系统会对某个没有命名的约束有多个重复的约束的产生，这样在我们插入，更新数据的时候，系统会根据约束的个数对某一个同样条件的约束产生多次无味的检测，这样会影响我们系统的性能，这一点是显而易见的，这里就不做试验了。

再有就是从上面的试验可以看到，只是有约束是check的约束有重复的现象，而其他，类似于关键字，唯一约束，非空约束，是没有产生重复的现象，这样并不是意味着其他的约束就可以不命名而让系统命名了，这里的主要原因是因为在定义关键字和唯一约束的时候，系统会有一个索引的产生。

SQL> select index_name,index_type,table_name from user_indexes where table_name='T';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME

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

SYS_C004828                    NORMAL                      T

SYS_C004829                    NORMAL                      T

SQL>

而我们在系统优化的时候，就会有需要对这个SQL文使用hint的时候，假如我们需要指定优化器使用主关键字索引来访问表的时候，就需要指定索引的名称，这样我们就必须指定索引为SYS_C004829的名称写入到SQL中，这样就会产生问题了，假如这个表被重建了，或者需要将数据库重新导入，导入等情况的时候，这时候系统就会自动对这些索引重新命名，我们优化SQL如果不能随之更新的话就不能达到我们的目的。所以说在设计表的时候，需要将约束的名填写上去，这样就可以避免以上事件的产生。