Re: constraints

From: Andy Finkenstadt <andy_at_homebase.vistachrome.com>
Date: Fri, 30 Oct 1992 17:15:11 GMT
Message-ID: <1992Oct30.171511.18896_at_homebase.vistachrome.com>


Whenever you have an 'R' referential integrity constraint FROM a table to another table, you need to search for the corresponding 'P' constraint type for the R_CONSTRAINT_NAME=CONSTRAINT_NAME.

For example:

1 select a.constraint_type, a.table_name, a.owner, 2 b.table_name, b.owner
3 from user_constraints a, user_constraints b 4 where a.constraint_type='R' /* find referential integrity */ 5 and b.r_owner=a.owner and b.r_constraint_name=a.constraint_name

-Andy

simon.rear_at_almac.co.uk writes:
>Hi,
>I've just written a sql report which prints all tables and indexes for a
>particular user. The contents include table name, comment constraint type and
>search condition etc.
>My question is - where does oracle store the references constraint detail's,
>ie which table and associated column is referenced for a constraint type of
>'R'.
>From the report I can see a particular column is referenced from the
>constraint type but to find which table and column it references at the moment
>I'd have to refer back to the sql command file which generated the table.
>Surely oracle must store this info in the data dictionary somewhere as it uses
>it when automatically writing default triggers in sqlforms or when a drop
>table command is issued. I've searched the data dictionary with no luck!
>Any Ideas ??
>Simon.Rear_at_Almac.Co.Uk

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Send mail to ora-request_at_vistachrome.com to join Unix, CASE, and 
  Desktop Oracle RDBMS Database discussions.
Received on Fri Oct 30 1992 - 18:15:11 CET

Original text of this message