List the column present in Foreign key constraint Oracle

List the column present in Foreign key constraint Oracle

Queries to get the details of foreign key present in the database including column details.
Note: you can also use ALL_* , DBA_* views for more details.

Find complete detail of tables with column involved in Foreign Key
Note: Foreign key that use more than one column then query show with comma sign

col FK_TABLE_NAME for a15
col PK_TABLE_NAME for a15
col FK_COLUMNS for a20
col PK_COLUMNS for a20
select distinct a.table_name "FK_TABLE_NAME",
(select listagg(column_name, ',' ) within group (order by position) from user_cons_columns where constraint_name = a.constraint_name) "FK_COLUMNS",
b.table_name "PK_TABLE_NAME",
(select listagg(column_name, ',' ) within group (order by position) from user_cons_columns where constraint_name = a.r_constraint_name) "PK_COLUMNS"
from user_constraints a,user_constraints b where a.r_constraint_name = b.constraint_name and a.constraint_type = 'R';

FK_TABLE_NAME   FK_COLUMNS           PK_TABLE_NAME   PK_COLUMNS
--------------- -------------------- --------------- --------------------
TESTFOREIGN     P_ID                 TESTPRIMARY     ID
FOREIGN_TEST    P_ID,P_SHOPID        PRIMARY_TEST    ID,SHOP_ID


Note: Second line of output show that foreign key is created on two columns.

Following Queries use different ways of fetching same information by following query executing one by one with more details:

Find the table having Foreign key relation in Schema
Note: you can also use ALL_* , DBA_* views for more details.
Query return the foreign key tables and primary key constraint name. By using this constraint name we have to fetch primary table detail.

col table_name for a15
col owner for a15
col column_name for a15
col primaryowner for a10
col primaryconstraintname for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",
a.r_constraint_name "PrimaryConstraintName" from user_constraints a, user_cons_columns b where
A.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_type = 'R' order by b.position;

C OWNER           TABLE_NAME      COLUMN_NAME     PrimaryOwn PrimaryConstrai
- --------------- --------------- --------------- ---------- ---------------
R TEST            TESTFOREIGN     P_ID            TEST       SYS_C008201
R TEST            FOREIGN_TEST    P_ID            TEST       SYS_C008203
R TEST            FOREIGN_TEST    P_SHOPID        TEST       SYS_C008203

List the Primary key constraint detail

col table_name for a15
col owner for a15
col column_name for a15
col primaryowner for a10
col primaryconstraintname for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",
a.r_constraint_name "PrimaryConstraintName" from user_constraints a, user_cons_columns b where
A.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_name = 'SYS_C008201';

C OWNER           TABLE_NAME      COLUMN_NAME     PrimaryOwn PrimaryConstrai
- --------------- --------------- --------------- ---------- ---------------
P TEST            TESTPRIMARY     ID

col table_name for a15
col owner for a15
col column_name for a15
col primaryowner for a10
col primaryconstraintname for a15
select a.constraint_type,a.owner,a.table_name,b.column_name,a.r_owner "PrimaryOwner",
a.r_constraint_name "PrimaryConstraintName" from user_constraints a, user_cons_columns b where
A.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.constraint_name = 'SYS_C008203';

C OWNER           TABLE_NAME      COLUMN_NAME     PrimaryOwn PrimaryConstrai
- --------------- --------------- --------------- ---------- ---------------
P TEST            PRIMARY_TEST    ID
P TEST            PRIMARY_TEST    SHOP_ID

Columns detail in User Constraints Table

SQL> desc user_constraints;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(128)
CONSTRAINT_NAME                                    VARCHAR2(128)
CONSTRAINT_TYPE                                    VARCHAR2(1)
TABLE_NAME                                         VARCHAR2(128)
SEARCH_CONDITION                                   LONG
SEARCH_CONDITION_VC                                VARCHAR2(4000)
R_OWNER                                            VARCHAR2(128)
R_CONSTRAINT_NAME                                  VARCHAR2(128)
DELETE_RULE                                        VARCHAR2(9)
STATUS                                             VARCHAR2(8)
DEFERRABLE                                         VARCHAR2(14)
DEFERRED                                           VARCHAR2(9)
VALIDATED                                          VARCHAR2(13)
GENERATED                                          VARCHAR2(14)
BAD                                                VARCHAR2(3)
RELY                                               VARCHAR2(4)
LAST_CHANGE                                        DATE
INDEX_OWNER                                        VARCHAR2(128)
INDEX_NAME                                         VARCHAR2(128)
INVALID                                            VARCHAR2(7)
VIEW_RELATED                                       VARCHAR2(14)
ORIGIN_CON_ID                                      NUMBER

USER_CONS_COLUMNS

SQL> desc user_cons_columns;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OWNER                                     NOT NULL VARCHAR2(128)
CONSTRAINT_NAME                           NOT NULL VARCHAR2(128)
TABLE_NAME                                NOT NULL VARCHAR2(128)
COLUMN_NAME                                        VARCHAR2(4000)
POSITION                                           NUMBER

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.