Foreign Keys by Example

=========================
Find out Referencing Tree:
=========================

SELECT FK_COLUMNS.table_name  AS REFERENCING_TABLE,

       FK_COLUMNS.column_name AS REFERENCING_COLUMN,

       FKS.constraint_name    AS REFERENCING_CONSTRAINT,

       PK_COLUMNS.table_name  AS PRIMARY_TABLE,

       PK_COLUMNS.column_name AS PRIMARY_COLUMN,    

       PKS.CONSTRAINT_NAME    AS PRIMARY_CONSTRAINT

  FROM USER_CONSTRAINTS PKS,

       USER_CONSTRAINTS FKS,

       USER_CONS_COLUMNS PK_COLUMNS,

       USER_CONS_COLUMNS FK_COLUMNS      

 WHERE PKS.constraint_type IN ( 'P' ,'U')

   AND PK_COLUMNS.constraint_name = FKS.r_constraint_name

   AND FK_COLUMNS.constraint_name=FKS.constraint_name

   AND FKS.constraint_type = 'R'

   AND FKS.r_constraint_name = PKS.constraint_name

   AND PKS.table_name LIKE '%MY_TABLE%'

   AND FKS.constraint_name = 'SOME_ID_FK';

REFERENCING_TABLE              REFERENCING_COLUMN   REFERENCING_CONSTRAINT         PRIMARY_TABLE        PRIMARY_COLUMN   PRIMARY_CONSTRAINT
------------------------------ -------------------- ------------------------------ -------------------- ---------------- --------------------
DEBUG_RAF_W_NEW_EDR            AFFILIATE_ID         DRWNE_AFFILIATE_ID_FK          GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
DEBUG_GEN_W_NEW_SDR            AFFILIATE_ID         DGWNS_AFFILIATE_ID_FK          GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SGA_BW_LIST_MME                AFFILIATE_ID         SBLMME_AFFILIATE_ID_FK         GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_COUNTRY_IBR_IMMED_REJ_NW   AFFILIATE_ID         ICIIRN_AFFILIATE_ID_FK         GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_COUNTRY_ECIS               AFFILIATE_ID         ICE_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_COUNTRY_TACS               AFFILIATE_ID         ICT_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_SS_BARRING                 AFFILIATE_ID         ISSB_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_W_ACTUAL_DISTRIBUTION_B    AFFILIATE_ID         IWADB_AFFILIATE_ID_FK          GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SFI_CUSTOMER_USAGE_HOURLY_TMP  AFFILIATE_ID         SCUHT_AFFILIATE_ID_FK          GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SFI_CUSTOMER_USAGE_HOURLY      AFFILIATE_ID         SCUH_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_COMMUNITY_PRIORITY         AFFILIATE_ID         ICP1_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SGA_MSISDN_MANIPULATIONS       AFFILIATE_ID         SMM_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SGA_W_IPN_SUBSCRIBER           AFFILIATE_ID         SWIS_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SYS_EXT_INTERFACES             AFFILIATE_ID         SEI_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
HSC_SUBSCRIBER_SHORT_CODES     AFFILIATE_ID         HSSC_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
HSC_LOCAL_SHORT_CODES          AFFILIATE_ID         HLSC_AFFILIATE_ID_FK           GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
HSC_SERVICE_PROPERTIES         AFFILIATE_ID         HSP_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
IPN_IBR_CELL_REPORT_DATA       AFFILIATE_ID         IICRD_AFFILIATE_ID_FK          GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK
SHORT_CODES                    AFFILIATE_ID         SC_AFFILIATE_ID_FK             GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK

ICA_CORRECT_IDDS               AFFILIATE_ID         ICI_AFFILIATE_ID_FK            GA_ACCOUNTS          NETWORK_ID       AFFILIATE_ID_UK


===============================
Find out Referencing Table by FK name:
===============================
SELECT * FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R'
  AND r_constraint_name = 'REFERENCE_CONSTRAINT'

===============================
Find out The Primary Key columns:
===============================
SELECT * FROM USER_CONS_COLUMNS 
 WHERE constraint_name = 'GA_PRODUCT_TREE_CONF_PK'


===============================
DELETE CASCADE
===============================
To be able to delete entries in Parent Table, 
the Foreign Key in Child Table should have been created with
 on delete cascade option:

ALTER TABLE EMP ADD 
(CONSTRAINT JOB_FK FOREIGN KEY (job_key) 
REFERENCES JOB (job_key) ON DELETE CASCADE);


===============================
How to Add ON DELETE CASCADE to an existing constraint?
===============================

It is not possible to add ON DELETE CASCADE to an existing constraint.
To do so, need to drop the constraint, and crate it again with ON DELETE CASCADE option.

For example:

ALTER TABLE CHILD_TABLE DROP CONSTRAINT FK_CONSTRAINT;
   
ALTER TABLE CHILD_TABLE
  ADD CONSTRAINT FK_CONSTRAINT 
  FOREIGN KEY (child_column) 
  REFERENCES PARENT_TABLE(PARENT_COLUMN) 

  ON DELETE CASCADE;   

===============================
Get DDL for FK
===============================
Get the DDL for all FK for some column 


SET LONG 1000
SET LINESIZE 400
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
COL GET_FK_DDL FOR A200

spool ddl_to_recreate_fk_constraints.sql

SELECT 'SET LONG 1000' FROM DUAL;
SELECT 'SET LINESIZE 400' FROM DUAL;
SELECT 'SET PAGESIZE 0' FROM DUAL;
SELECT 'SET HEADING OFF' FROM DUAL;
SELECT 'SET FEEDBACK OFF' FROM DUAL;
SELECT 'SPOOL recreate_fk_constraints.sql' FROM DUAL;

SELECT 'SELECT DBMS_METADATA.get_ddl(''REF_CONSTRAINT'', '||''''||FK_constraint||''''||','||''''||FK_owner||''''||')||'';'' FROM DUAL;' AS GET_FK_DDL
--     , 'DROP CONSTAINT '||FK_constraint||';' AS GET_DROP_FK_SQL
FROM 
(
SELECT FKS.owner AS FK_owner, 
       FKS.constraint_name AS FK_constraint, 
       FKS.table_name AS FK_table, 
       PKS.owner AS PK_owner, 
       PKS.constraint_name AS PK_constraint, 
       PKS.table_name AS PK_TABLE
FROM USER_CONSTRAINTS PKS,
     USER_CONSTRAINTS FKS
  WHERE PKS.constraint_type = 'P' 
    AND FKS.constraint_type = 'R' 
    AND FKS.r_constraint_name = PKS.constraint_name
    AND FKS.r_constraint_name IN
   (SELECT constraint_name FROM USER_CONS_COLUMNS WHERE column_name = 'NETWORK_ID')
)FK_LIST;
SELECT 'spool off;' FROM DUAL;
spool off


Get the SQL Command to drop all FK for some column 


SET LONG 1000
SET LINESIZE 400
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
COL GET_FK_DDL FOR A200

spool drop_fk_constraints.sql

SELECT 'ALTER TABLE '||FK_table||' DROP CONSTRAINT '||FK_constraint||';' AS GET_DROP_FK_SQL
FROM 
(
SELECT FKS.owner AS FK_owner, 
       FKS.constraint_name AS FK_constraint, 
       FKS.table_name AS FK_table, 
       PKS.owner AS PK_owner, 
       PKS.constraint_name AS PK_constraint, 
       PKS.table_name AS PK_TABLE
FROM USER_CONSTRAINTS PKS,
     USER_CONSTRAINTS FKS
  WHERE PKS.constraint_type = 'P' 
    AND FKS.constraint_type = 'R' 
    AND FKS.r_constraint_name = PKS.constraint_name
    AND FKS.r_constraint_name IN
   (SELECT constraint_name FROM USER_CONS_COLUMNS WHERE column_name = 'NETWORK_ID')
)FK_LIST;

spool off

The output is:
SELECT DBMS_METADATA.get_ddl('REF_CONSTRAINT', 'BILLING_PROCESS_FK','NPPROV') FROM DUAL;

=============================== To disable constraint:
===============================
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

===============================
Add Foreign Key example
===============================
ALTER TABLE SECOND_TABLE ADD CONSTRAINT fk_emp_id FOREIGN KEY (person_id) REFERENCES EMPLOYEE (emp_id);