Re: Decoding constraint names

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Tue, 06 Jul 1999 14:36:09 GMT
Message-ID: <01bec7cd$8a010ba0$a504fa80@mndnet>

Hi -

The table names are all_constraints, user_constraints, dba_constraints and corresponding all_cons_columns, user_cons_coulmns etc.

Constraint_type decode in these tables:

	C = Check
	P = Primary key
	R = Foreign key
	U = Unique key
	V = With Check option 

Here are couple of scripts that you may want to play with.

SELECT

    A.TABLE_NAME table_name,
    A.CONSTRAINT_NAME key_name,
    B.TABLE_NAME referencing_table,
    B.CONSTRAINT_NAME foreign_key_name,
    B.STATUS fk_status

  FROM all_CONSTRAINTS A, all_CONSTRAINTS B   WHERE
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and     B.CONSTRAINT_TYPE = 'R'
  ORDER BY 1, 2, 3, 4
/

select c.table_name TABLE_NAME, cc.column_name COLUMN_NAME, substr('('||cc.position||')',1,8) POSITION from dba_cons_columns cc, dba_constraints c where c.constraint_name=cc.constraint_name and c.constraint_type='P'
/

Later !!!

Suresh Bhat
Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Sam Jordan <sjo_at_spin.ch> wrote in article <3782001A.584A8D6C_at_spin.ch>...
> 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:36:09 CDT