Finding table dependencies (foreign key constraints) in Oracle – walking the dependency tree

We’ve all had the experience of cleaning up testing data or needing to remove a couple records from a table, only to be greeted by a foreign key constraint error. If you are unfamiliar with a database, you will be left wondering how many times you’ll need to follow foreign keys before finally finding the parent table that will allow you to delete the required records.

So how can we find all the tables that are dependent on a particular table? This question introduces two Oracle concepts. The first is a DBA view named DBA_CONSTRAINTS. The second is the concept of recursive queries. First, let’s take a brief look at the view:

SQL> DESC dba_constraints
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(120)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(120)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 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(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

Using the HR schema as an example, let’s look at the constraints on the REGIONS table:

SQL> SELECT owner, table_name, constraint_name, r_constraint_name
  2  FROM dba_constraints
  3  WHERE owner = 'HR'
  4    AND table_name = 'REGIONS';

OWNER      TABLE_NAME                     CONSTRAINT_NAME                R_CONSTRAINT_NAME
---------- ------------------------------ ------------------------------ ------------------
HR         REGIONS                        REG_ID_PK
HR         REGIONS                        REGION_ID_NN

The R_CONSTRAINT_NAME column captures the referred constraint of a foreign key. Notice there are no referential constraints on this table. In fact, it’s a parent table of a few other tables. Let’s take a look at the constraints on the COUNTRIES table to see what a foreign key looks like. COUNTR_REG_FK on the COUNTRIES table references REG_ID_PK.

SQL> SELECT owner, table_name, constraint_name, r_constraint_name
  2  FROM dba_constraints
  3  WHERE owner = 'HR'
  4    AND table_name = 'COUNTRIES';

OWNER      TABLE_NAME                     CONSTRAINT_NAME                R_CONSTRAINT_NAME
---------- ------------------------------ ------------------------------ -------------------
HR         COUNTRIES                      COUNTR_REG_FK                  REG_ID_PK
HR         COUNTRIES                      COUNTRY_C_ID_PK
HR         COUNTRIES                      COUNTRY_ID_NN

Next, let’s consider using a recursive query. Let’s say we want to clear the REGIONS table and need to know all other tables that need to be cleared to accomplish this. The following query will walk up multiple instances of the DBA_CONSTRAINTS view, revealing all constraints (and, in turn, tables) that are dependent on REGIONS.

SQL> SELECT c.owner, c.table_name, c.constraint_name, level
  2  FROM dba_constraints r, dba_constraints c
  3  WHERE r.owner = c.r_owner
  4    AND r.constraint_name = c.r_constraint_name
  5  START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  6  CONNECT BY NOCYCLE r.owner = PRIOR c.owner
  7    AND r.table_name = PRIOR c.table_name
  8  ORDER BY level;

OWNER      TABLE_NAME                     CONSTRAINT_NAME                     LEVEL
---------- ------------------------------ ------------------------------ ----------
HR         COUNTRIES                      COUNTR_REG_FK                           1
HR         LOCATIONS                      LOC_C_ID_FK                             2
HR         DEPARTMENTS                    DEPT_LOC_FK                             3
HR         EMPLOYEES                      EMP_DEPT_FK                             4
HR         JOB_HISTORY                    JHIST_DEPT_FK                           4
HR         JOB_HISTORY                    JHIST_EMP_FK                            5

6 rows selected.

Now, let’s use the above query as a basis to generate scripts that disable dependent constraints, clear relevant tables, and re-enable constraints. RUNNING THE GENERATED SCRIPTS WILL CLEAR MOST TABLES IN YOUR HR PRACTICE SCHEMA!!!

SQL> SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' DISABLE CONSTRAINT ' || c.constraint_name || ';'
  2  FROM dba_constraints r, dba_constraints c
  3  WHERE r.owner = c.r_owner
  4    AND r.constraint_name = c.r_constraint_name
  5  START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  6  CONNECT BY NOCYCLE r.owner = PRIOR c.owner
  7    AND r.table_name = PRIOR c.table_name;

'ALTERTABLE'||C.OWNER||'.'||C.TABLE_NAME||'DISABLECONSTRAINT'||C.CONSTRAINT_NAME||';'
-----------------------------------------------------------------------------------------
ALTER TABLE HR.JOB_HISTORY DISABLE CONSTRAINT JHIST_DEPT_FK;
ALTER TABLE HR.LOCATIONS DISABLE CONSTRAINT LOC_C_ID_FK;
ALTER TABLE HR.EMPLOYEES DISABLE CONSTRAINT EMP_DEPT_FK;
ALTER TABLE HR.DEPARTMENTS DISABLE CONSTRAINT DEPT_LOC_FK;
ALTER TABLE HR.COUNTRIES DISABLE CONSTRAINT COUNTR_REG_FK;
ALTER TABLE HR.JOB_HISTORY DISABLE CONSTRAINT JHIST_EMP_FK;

6 rows selected.

SQL>
SQL> SELECT DISTINCT 'DELETE FROM ' || c.owner || '.' || c.table_name || ';'
  2  FROM dba_constraints r, dba_constraints c
  3  WHERE r.owner = c.r_owner
  4    AND r.constraint_name = c.r_constraint_name
  5  START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  6  CONNECT BY NOCYCLE r.owner = PRIOR c.owner
  7    AND r.table_name = PRIOR c.table_name;

'DELETEFROM'||C.OWNER||'.'||C.TABLE_NAME||';'
-----------------------------------------------------------------------------------------
DELETE FROM HR.DEPARTMENTS;
DELETE FROM HR.EMPLOYEES;
DELETE FROM HR.JOB_HISTORY;
DELETE FROM HR.COUNTRIES;
DELETE FROM HR.LOCATIONS;

SQL> SELECT DISTINCT 'ALTER TABLE ' || c.owner || '.' || c.table_name || ' ENABLE CONSTRAINT ' || c.constraint_name || ';'
  2  FROM dba_constraints r, dba_constraints c
  3  WHERE r.owner = c.r_owner
  4    AND r.constraint_name = c.r_constraint_name
  5  START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  6  CONNECT BY NOCYCLE r.owner = PRIOR c.owner
  7    AND r.table_name = PRIOR c.table_name;

'ALTERTABLE'||C.OWNER||'.'||C.TABLE_NAME||'ENABLECONSTRAINT'||C.CONSTRAINT_NAME||';'
-----------------------------------------------------------------------------------------
ALTER TABLE HR.JOB_HISTORY ENABLE CONSTRAINT JHIST_EMP_FK;
ALTER TABLE HR.JOB_HISTORY ENABLE CONSTRAINT JHIST_DEPT_FK;
ALTER TABLE HR.LOCATIONS ENABLE CONSTRAINT LOC_C_ID_FK;
ALTER TABLE HR.DEPARTMENTS ENABLE CONSTRAINT DEPT_LOC_FK;
ALTER TABLE HR.COUNTRIES ENABLE CONSTRAINT COUNTR_REG_FK;
ALTER TABLE HR.EMPLOYEES ENABLE CONSTRAINT EMP_DEPT_FK;

6 rows selected.

We can also use a similar approach to simply generate the proper sequence of delete statements to clear these tables without disabling constraints. Notice in the query below, some tables occur more than once when walking the dependency tree. Multiple tables can have dependencies on a single table, and a record will be returned for each one of those dependencies.

SQL> SELECT c.owner, c.table_name, c.constraint_name, MAX( level ) max_level
  2  FROM dba_constraints r, dba_constraints c
  3  WHERE r.owner = c.r_owner
  4    AND r.constraint_name = c.r_constraint_name
  5  START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  6  CONNECT BY NOCYCLE r.owner = PRIOR c.owner
  7    AND r.table_name = PRIOR c.table_name
  8  GROUP BY c.owner, c.table_name, c.constraint_name
  9  ORDER BY max_level DESC;

OWNER      TABLE_NAME                     CONSTRAINT_NAME                 MAX_LEVEL
---------- ------------------------------ ------------------------------ ----------
HR         JOB_HISTORY                    JHIST_EMP_FK                            5
HR         EMPLOYEES                      EMP_DEPT_FK                             4
HR         JOB_HISTORY                    JHIST_DEPT_FK                           4
HR         DEPARTMENTS                    DEPT_LOC_FK                             3
HR         LOCATIONS                      LOC_C_ID_FK                             2
HR         COUNTRIES                      COUNTR_REG_FK                           1

6 rows selected.

So, when we generate the delete statements, we need to be sure to pull only the MAX() level for each table in the dependency tree and sort by those levels. For the most part, this helps ensure tables will be deleted in an order that avoids referential integrity violations. However, this is not 100% true, as we will see soon.

SQL> SELECT 'DELETE FROM ' || owner || '.' || table_name || ';'
  2  FROM
  3  (
  4    SELECT c.owner, c.table_name, MAX( level ) max_level
  5    FROM dba_constraints r, dba_constraints c
  6    WHERE r.owner = c.r_owner
  7      AND r.constraint_name = c.r_constraint_name
  8    START WITH r.owner = 'HR' AND r.table_name = 'REGIONS'
  9    CONNECT BY NOCYCLE r.owner = PRIOR c.owner
 10      AND r.table_name = PRIOR c.table_name
 11    GROUP BY c.owner, c.table_name
 12  )
 13  ORDER BY max_level DESC;

'DELETEFROM'||OWNER||'.'||TABLE_NAME||';'
-----------------------------------------------------------------------------------
DELETE FROM HR.JOB_HISTORY;
DELETE FROM HR.EMPLOYEES;
DELETE FROM HR.DEPARTMENTS;
DELETE FROM HR.LOCATIONS;
DELETE FROM HR.COUNTRIES;

Now, if you attempt to run these delete statements, you’ll find that this order still does not avoid a foreign key violation, namely DEPT_MGR_FK. With a little more research, you’ll realize that DEPARTMENTS has a foreign key to EMPLOYEES. In turn, EMPLOYEES also has a foreign key back to DEPARTMENTS. This circular set of references prevents using only deletes to clear the tables. No matter the order in which you choose to delete these tables, you will violate a foreign key. This is why the disable constraint, delete, enable constraint approach is a better to avoid referential key violations. However, both approaches are provided as either will work in the majority of instances.

Advertisements

One thought on “Finding table dependencies (foreign key constraints) in Oracle – walking the dependency tree

  1. Pingback: Script – Disable FK Constraints Referencing a Table | brainFizzle

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s