[Gusdev-gusdev] queries to find constraints

[Steve F. <st...@pc...>]

Thomas and others-

See below for queries to find out the details of a constraint in 
oracle.  Use this if you get messages from oracle describing an 
integrity constraint error and you want to know what foreign key was 
involved.

steve

-------- Original Message --------
Subject: 	Constraint Query
Date: 	Mon, 2 Feb 2004 18:25:55 -0500
From: 	Michael Saffitz <msa...@pc...>
To: 	Steve Fischer <st...@pc...>



Steve,

This is from Table.pm (sub getConstraintsSql), which is part of GUS--> 
gushome/lib/perl/GUS/DBAdmin

constraints on table $owner.$name

select *  from all_constraints c where c.owner = upper('$owner') and 
c.table_name = upper('$name')

for a given $owner.$name table.

foreign key constraints on table $owner.$name:

select ac1.* from all_constraints ac1, all_constraints ac2 where 
ac1.constraint_type = 'R' and ac1.r_constraint_name = 
ac2.constraint_name and ac1.r_owner = ac2.owner and ac2.owner = 
upper('${owner}') and ac2.table_name = upper('${name}')

--Mike