[SQL] Query to check FK(FOREIGN KEY) Constraint

by , under Oracle

How to check FK (foreign key) constraint using query in Oracle database

Example 1)

deptno column on dept table is referenced by deptno pk column on emp table.

### dept(pk : deptno) -> emp(fk : deptno)

SQL> alter table dept add constraints pk_deptno primary key (deptno);

Table altered.
Elapsed: 00:00:00.40

SQL> alter table emp add constraints fk_deptno
foreign key (deptno)
references dept
/

 

fk_const_to.sql

DOC
file: fk_const.sql
input : child
output : parent
EX) dept(pk : deptno) -> emp(fk : deptno)
if the input is emp, then the output is dept
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated ,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and b.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

 

Result :

Enter value for table_name: emp
old 16: and b.table_name = upper('&table_name')
new 16: and b.table_name = upper('emp')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

fk_const_from.sql

DOC
file: fk_const_from.sql
input : parent
output : child
dept(pk : deptno) -> emp(fk : deptno)
if the input is dept, then the output is emp
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and a.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

Enter value for table_name: dept
old 16: and a.table_name = upper('&table_name')
new 16: and a.table_name = upper('dept')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

 

Leave a Reply