Where Did All of These Constraints Come From?

a@10G> create table t (x number constraint t_chk check (x >0));

a@10G> create table t1(x number check (x > 0));

a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
  2  FROM all_constraints
  3  WHERE owner ='A'
  4  AND table_name IN ('T','T1');

TABLE_NAME CONSTRAINT_NAME      CONST SEARCH_CON
---------- --------------------                         ----- ----------
T1         SYS_C0011225                             C         x > 0
T          T_CHK                                         C         x >0



Now we have a Named Check constraint bounded to table T and System Generated Constraint name bounded to table T1. Now let us see the Impact of having System generated constraint name.


D:\>exp userid=a/a tables=(T,T1) file=d:\a_10g.dmp

Export: Release 10.2.0.3.0 - Production on Thu Mar 4 18:55:49 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
. . exporting table                             T1          0 rows exported
Export terminated successfully without warnings.


D:\>imp userid=a/a file=d:\a_10g.dmp ignore=y

Import: Release 10.2.0.3.0 - Production on Thu Mar 4 18:56:21 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing A's objects into A
. importing A's objects into A
. . importing table                            "T"          0 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
 "ALTER TABLE "T" ADD CONSTRAINT "T_CHK" CHECK (x >0) ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table                           "T1"          0 rows imported
About to enable constraints...
Import terminated successfully with warnings.





Now we can see that Named Constraints failed, because Constraint T_CHK is already bounded to table 'T'. but that doesn't happen with System Generated Constraint name



a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
  2  FROM all_constraints
  3  WHERE owner ='A'
  4  AND table_name IN ('T','T1');

TABLE_NAME CONSTRAINT_NAME      CONST SEARCH_CON
---------- --------------------                         -----         ----------
T1         SYS_C0011225                             C         x > 0
T1         SYS_C0011226                             C         x > 0
T          T_CHK                                        C         x >0

Then importing multiple times ,  we have redundant constraint added to T1 but not in T& Over the constraints they accumulated hundreds of check constraint on table T1.


a@10G> SELECT table_name, constraint_name, constraint_type, search_condition
  2  FROM all_constraints
  3  WHERE owner ='A'
  4  AND table_name IN ('T','T1');

TABLE_NAME CONSTRAINT_NAME      CONST SEARCH_CON
---------- -------------------- ----- ----------
T1         SYS_C0011225         C     x > 0
T1         SYS_C0011226         C     x > 0
T          T_CHK                      C     x >0
T1         SYS_C0011227         C     x > 0
T1         SYS_C0011228         C     x > 0
T1         SYS_C0011229         C     x > 0
T1         SYS_C0011230         C     x > 0
T1         SYS_C0011231         C     x > 0
T1         SYS_C0011232         C     x > 0
T1         SYS_C0011233         C     x > 0
T1         SYS_C0011234         C     x > 0
T1         SYS_C0011235         C     x > 0
T1         SYS_C0011236         C     x > 0
T1         SYS_C0011237         C     x > 0
T1         SYS_C0011238         C     x > 0
T1         SYS_C0011239         C     x > 0
T1         SYS_C0011240         C     x > 0
T1         SYS_C0011241         C     x > 0
T1         SYS_C0011242         C     x > 0
T1         SYS_C0011243         C     x > 0
T1         SYS_C0011244         C     x > 0
T1         SYS_C0011245         C     x > 0
T1         SYS_C0011246         C     x > 0
T1         SYS_C0011247         C     x > 0
T1         SYS_C0011248         C     x > 0
T1         SYS_C0011249         C     x > 0
T1         SYS_C0011250         C     x > 0
T1         SYS_C0011251         C     x > 0
T1         SYS_C0011252         C     x > 0

29 rows selected.

Now lets us check the effect of having redundant check constraints with impact to performance.

a@10G> insert into T(x)
  2  select level
  3  from dual
  4  connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:02.70
a@10G> insert into T1(x)
  2  select level
  3  from dual
  4  connect by level <= 1000000;

1000000 rows created.

Elapsed: 00:00:07.13


Yet another good reason to name your constraints!