Finding constraint information

Published Mon 22 Mar, 07:41 by admin in oracle

Data dictionary views are everything

Often enough when debugging or viewing your logs in your Java or .NET (or other) apps you'd stumble upon something with a name "SYS_C005049" that would say Constraint violated.

You'd ask yourself, why couldn't they provide a meaningful name, well that happens when you don't name table constraints with something proper that would indicate the table name and its referencing table name.

Fret not!

Execute this query: select * from ALL_CONS_COLUMNS where table_name = 'Mytable';

With a list of columns: OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION

To Search by constraint name:  select * from ALL_CONS_COLUMNS where constraint_name = 'SYS_C005049'

Enjoy