Re: Question about foreign keys

duffy_at_cais.cais.com (MCC) wrote:

>Hi All,
 

> I have a foreign key and need to find the table/column
>it is referencing, I've tried DBA_CONS_COLUMNS and DBA_CONSTRAINTS
>and could not get the clue, please help.
 

>Thanks,
>MCC
FOLLOW THROUGH THE EXAMPLE AND IF YOU HAVE ANY QUESTIONS, PLEASE LET ME KNOW. SQL> create table primary_table (

  2     sno number
  3      constraint primary_table_pk primary key,
  4     name varchar2(10));

Table created.

SQL> create table foreign_table (

  2     sno number
  3      constraint foreign_table_fk references primary_table(sno),
  4     age number);

Table created.

FOREIGN_TABLE'S SNO FIELD IS REFERENCING PRIMARY_TABLE'S SNO. BUT, YOU DON'T KNOW THIS UNLESS
I TELL YOU. BUT, ORACLE KEEPS TRACK OF THESE CONSTRAINTS IN A DATA DICTIONARY TABLE
CALLED USER_CONSTRAINTS. SQL> describe user_constraints

 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                 NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                          VARCHAR2(1)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                         LONG
 R_OWNER                                  VARCHAR2(30)
 R_CONSTRAINT_NAME                        VARCHAR2(30)
 DELETE_RULE                              VARCHAR2(9)
 STATUS                                   VARCHAR2(8)

YOU KNOW THE CONSTRAINT NAME ON YOUR TABLE. IF YOU DID NOT NAME YOUR CONSTRAINTS WHILE
CREATING TABLES, ORACLE WILL GENERATE CONSTRAINT NAMES LIKE SYS_C90821. IN THIS EXAMPLE,
I NAMED MY CONSTRAINT AS FOREIGN_TABLE_FK. SQL> select * from user_constraints
  2 where constraint_name = 'FOREIGN_TABLE_FK';

OWNER                          CONSTRAINT_NAME                C
TABLE_NAME                          
------------------------------ ------------------------------ -
------------------------------      

R_OWNER                        R_CONSTRAINT_NAME
DELETE_RU STATUS                      
------------------------------ ------------------------------
--------- --------                    
KRISHNA                        FOREIGN_TABLE_FK               R
FOREIGN_TABLE                       


KRISHNA                        PRIMARY_TABLE_PK               NO
ACTION ENABLED                     

FOREIGN_TABLE_FK IS REFERENCING THE CONSTRAINT PRIMARY_TABLE_PK. THAT IS SHOWN UNDER
R_CONSTRAINT_NAME FIELD. FIND OUT WHICH TABLE HAS THE PRIMARY_TABLE_PK AS SHOWN BELOW:
UNDER TABLE_NAME FIELD, YOU WILL FIND PRIMARY_TABLE. THIS IS YOUR ANSWER. SQL> select * from user_constraints
  2 where constraint_name = 'PRIMARY_TABLE_PK';

OWNER                          CONSTRAINT_NAME                C
TABLE_NAME                          
------------------------------ ------------------------------ -
------------------------------      

R_OWNER                        R_CONSTRAINT_NAME
DELETE_RU STATUS                      
------------------------------ ------------------------------
--------- --------                    
KRISHNA                        PRIMARY_TABLE_PK               P
PRIMARY_TABLE