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
