Re: getting foreign key dependencies

From: Nathan Secrist <nsecrist_at_evolving.com>
Date: 1997/06/11
Message-ID: <339EEB2F.6B50@evolving.com>#1/1

Hans Verbrugge wrote:
>
> Hi,
>
> Is there a simple way to check if a column is a reference to the
> primary key of another table?

The following will give you foreign keys that reference primary keys.

select * from user_constraints where r_constraint_name is not null;

>
> I need to get the names of the table and column where a foreign
> key points to.

This gives you table and column where foreign key is pointing to

select substr(a.CONSTRAINT_NAME,1,30) as Constraint,

       decode(b.constraint_type,
          'P', 'Primary',
          'U', 'Unique',
          'C', 'Check or Not Null',
          'R', 'Foreign') as Constraint_Type,
       substr(b.r_constraint_name, 1, 30) as Key,
       substr(a.table_name, 1, 20) as Owner_Table,
       substr(a.column_name, 1, 30) as Owner_Column
from user_cons_columns a,
     user_constraints b

where a.constraint_name = b.constraint_name and a.constraint_name = 'Foreign_key_name';

>
> Thanks,
>
> - hans
 

-- 
Nathan

-----------------------------------------------------------------
|  Nathan G. Secrist                                            |
-----------------------------------------------------------------
|  Evolving Systems, Inc.     |  Phone: (303) 802-1307          |
|  9777 Mount Pyramid Court   |    Fax: (303) 802-1420          |
|  Englewood, CO 80112        |  Email: nsecrist_at_evolving.com   |
-----------------------------------------------------------------

If it doesn't work, don't worry! If it did, you'd be out of a job!

Received on Wed Jun 11 1997 - 00:00:00 CDT