Re: Getting the foreign key constraint info.

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 30 Jun 2005 07:05:45 -0700
Message-ID: <1120140354.909588@yasure>

Archie wrote:
> Hi
>
> We know that the syntax to create a foreign key constraint is as
> follows
>
> foreign key(foreignkeycolumn,foreignkeycolumn,...) references
> foreigntable
>
> Now in this the constraint name, the foreignkeycolumn can be easily
> obtained using the user_constraints and User_cons_columns tables.
> However can someone tell me where is the foreigntable name is stored?
>
> Thanks

SELECT t.owner CHILD_OWNER,

t.table_name CHILD_TABLE,
t.constraint_name FOREIGN_KEY_NAME,
r.owner PARENT_OWNER,
r.table_name PARENT_TABLE,
r.constraint_name PARENT_CONSTRAINT

FROM user_constraints t, user_constraints r WHERE t.r_constraint_name = r.constraint_name

AND t.r_owner = r.owner
AND t.constraint_type='R'
AND t.table_name = <child_table_name>;

or a variation on the theme.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu Jun 30 2005 - 09:05:45 CDT