Re: describe a constraint (index / unique)

simo_at_sentissiweb.com (Simo Sentissi) wrote in message news:<396d3a87.0412021943.7336f0c2_at_posting.google.com>...
> Hello there
>
> I am new to oracle and I do not know how to describe a constraint, -I
> can describe a table from isqlplus- but can't describe constraint ?
> (primary key , index , relashioship ?)
>
> thanks

Describe works on most objects, but not constraints. There is a view containing such information you can query: user_constraints. I would look at the following columns:

constraint_name
r_constraint_name (for referential constraints [foreign keys]) table_name
index_name

A pripmary key will ahve a constraint_type of 'P', and a foreign key will have a constraint_type of 'R'; the following query will return the name, table and index for all primary keys defined by the user:

select constraint_name, table_name, index_name from user_constraints
where constraint_type = 'P';

For all foreign keys defined:

select constraint_name, r_constraint_name, table_name, index_name from user_constraints
where constraint_type = 'R';

The problems associated with unindexed foreign keys have been discussed here at length in other threads. To give the short version: unindexed foreign keys will create table locks on insert, update and delete operations on the referencing table. Should you find tables from the second query (returning information on foreign keys) having no indexes I would strongly urge you to consider creating them.

I hope this has sufficiently answered your question.

David Fitzjarrell Received on Fri Dec 03 2004 - 11:10:15 CST