Re: Decoding constraint names

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Tue, 06 Jul 1999 10:41:13 -0400
Message-ID: <37821589.707CCB37@Unforgettable.com>

Actually, if you look in views like DBA_CONSTRAINTS and ALL_CONSTRAINTS you will see how the constraint is defined. It may also be an index and that can be accessed via DBA_INDEXES or ALL_INDEXES. One of the ways that constraints with names such as SYS_C002390 are created when you create a table something like this:

CREATE TABLE MYTABLE (COL1 VARCHAR2(10) NOT NULL,

                                                   COL2 VARCHAR2(10)
NULL,
                                                   UNIQUE  (COL1))

TABLESPACE MYTABLESPACE; This will create a unique index on COL1 in MYTABLE, but since you did not specify a name for the constraint the rdbms will generate a name. If you want to avoid this and give the constraint a name, it would be re-written as:

CREATE TABLE MYTABLE (COL1 VARCHAR2(10) NOT NULL,

                                                   COL2 VARCHAR2(10)
NULL,
                                                   CONSTRAINT NDX_MYTABLE

UNIQUE (COL1))
TABLESPACE MYTABLESPACE; However, be warned that if you do it this way the index will be created in the same tablespace as the table and the storage will be derived from the default storage of the tablespace. If you want to change this after creation you'll have to do an
ALTER INDEX NDX_MYINDEX STORAGE (.......) TABLESPACE XXXX; (and supply your own values).

Constraints with names like this are also created when you specify that a column is not null, so both of the statements above would have created a system-generated constraint. To avoid this, rewrite it as:

CREATE TABLE MYTABLE
(COL1 VARCHAR2(10) CONSTRAINT MYTABLE_COL1_NOTNULL NOT NULL,   COL2 VARCHAR2(10),
  CONSTRAINT NDX_MYTABLE UNIQUE (COL1)) TABLESPACE MYTABLESPACE; If you really get ambitious you can go back and clean up all of the contraints with system-generated names except for those owned by SYS and SYSTEM. But, in general it is not worth the bother because you can always use the DBA_CONSTRAINTS view to figure out the structure of the constraints or indexes.

Ken

Sam Jordan wrote:

> Hi
>
> I get very often error messages like
>
> 'unique constraint (xxx.SYS_C002390) violated'.
>
> Unfortunately I don't know how to find out, which constraint has been
> violated, as this identification name doesn't say anything useful. I
> know
> there are some tables dealing with contraints, but I couldn't figure
> out how to get my information with few effort.
>
> bye
> --
> Sam Jordan
Received on Tue Jul 06 1999 - 09:41:13 CDT