User_constraints,user_cons_columns View foreign keys

Oracle looks at the constraint relationship between the primary key and the foreign key for a table, and the statement that is viewed:

Select A.owner Primary Key owner

, A.table_name primary key table

, B.column_name primary key columns

, C.owner foreign key owner

, C.table_name foreign key table

, D.column_name foreign key columns
From User_constraints A
Left JOIN User_cons_columns b

On A.constraint_name=b.constraint_name
Left JOIN User_constraints C

On C.r_constraint_name=a.constraint_name
Left JOIN User_cons_columns D

On C.constraint_name=d.constraint_name
where a.constraint_type= ' P '

and A.table_name= ' XXX '-tables that need to see the primary foreign key relationship
ORDER BY A.table_name

A kind of writing in Ning Wai

Select
A.owner FOREIGN key owner,
A.table_name foreign key table,
SUBSTR (c.column_name,1,127) foreign key column,
B.owner PRIMARY Key owner,
B.table_name primary key table,
SUBSTR (d.column_name,1,127) primary key column
From
User_constraints A,
User_constraints B,
User_cons_columns C,
User_cons_columns D
where
A.r_constraint_name=b.constraint_name
and a.constraint_type= ' R '
and b.constraint_type= ' P '
and A.r_owner=b.owner
and A.constraint_name=c.constraint_name
and B.constraint_name=d.constraint_name
and A.owner=c.owner
and A.table_name=c.table_name
and B.owner=d.owner
and B.table_name=d.table_name

Data dictionary table column Description:

Desc user_constraints

Name	Comments
OWNER	The owner of the table
Constraint_name	Constraint name
Constraint_type	Constraint type
table_name	Name associated with table with constraint definition
Search_condition	Text of search condition for table check
R_owner	Owner of table used in referential constraint
R_constraint_name	Name of UNIQUE constraint definition for referenced table
Delete_rule	The delete rule for a referential constraint
STATUS	Enforcement status of Constraint-enabled or DISABLED
Deferrable	is the constraint deferrable-deferrable or not deferrable
DEFERRED	Is the constraint deferred by default-deferred or IMMEDIATE
Validated	Is this constraint system validated? -Validated or not validated
Generated	Was the constraint name system generated? -Generated NAME or USERNAME
Bad	Creating this constraint should give ORA-02436. Rewrite it before2000 AD.
RELY	If set, this flag is used in optimizer
Last_change	The date this column is last enabled or disabled
Index_owner	The owner of the index used by the constraint
Index_name	The index used by the constraint
INVALID
view_related

Desc User_cons_columns;

Name	Default Comments
OWNER	Owner of the constraint definition
Constraint_name	Name associated with the constraint definition
table_name	Name associated with table with constraint definition
column_name	Name associated with column or attributes of object column specified in the constraint definition
POSITION	Original position of column or attribute in definition