Finding Foreign Key Constraints in Oracle

http://www.databasejournal.com/features/oracle/article.php/3665591

Many times developers find it difficult to delete records from parent tables when child records reference those tables through foreign key (referential integrity) constraints.

Constraints validate the data; without constraints, we are just storing invalid data.

For a developer to identify and disable foreign key constraints is a difficult task. Most of the time, the application’s ER diagrams are not available to the developers. A brief description on the foreign key (referential integrity) constraints will go a long way in identifying and disabling these constraints.

Unless a primary/unique key constraint is enabled on the parent key column, Oracle does not allow enabling a foreign key constraint on a child key column. Primary/unique keys on the parent key column will not allow duplicate values.

A table can have only one primary key constraint. A table can have multiple unique key constraints.

The enabled foreign key constraint will not allow inserting child records in the table, unless a matching record is found in the parent table.

In essence, constraints safeguard and validate the data.

Each parent record can have multiple child records, but each child can relate to ONLY one parent record. That is why Oracle wants a PRIMARY/ UNIQUE KEY index created on the PRIMARY KEY column of the PARENT TABLE. It is not mandatory to have such an index created on the CHILD KEY column in the CHILD TABLE.

The parent table can not be truncated, nor deleted, when foreign key(referential integrity) constraints are referencing them.

SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
  2  from all_constraints where constraint_type in ('P','U') and table_name='TEMP_JP1';

The r_constraint_name( primary/unique constraint name in the parent table) column in the all_constraints view is referenced by the constraint_name (foreign key(referential integrity) constraint in the child table), when the constraint_type is ‘R’. Using this definition, let us find out all the foreign key(referential integrity) constraints referencing the TEMP_JP1 table:

SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
  2   from all_constraints
  3  where constraint_type='R'
  4  and r_constraint_name in (select constraint_name from all_constraints
  5  where constraint_type in ('P','U') and table_name='TEMP_JP1');

Foreign key constraints can only be enabled on a child table, when a PRIMARY/UNIQUE KEY constraint is enabled on the parent table.

As long as the foreign key constraints are enabled on the child tables, the data in the parent table is protected.

If we need to manipulate the data in the parent table, TEMP_JP1, first disable the foreign key(referential integrity) constraints on the child tables that are referencing the parent table.

A ready built script to identify and disable/enable foreign key constraints on child tables:

SQL> select 'alter table '||a.owner||'.'||a.table_name||
  2          ' disable constraint '||a.constraint_name||';'
  3          from all_constraints a, all_constraints b
  4          where a.constraint_type = 'R'
  5          and a.r_constraint_name = b.constraint_name
  6          and a.r_owner  = b.owner
  7          and b.table_name = 'TEMP_JP1';

Please be careful: Both Primary / Unique key indexes do not allow duplicates in the column. All primary key indexes are unique. A table can have only one Primary key index and can have multiple unique key indexes. One main difference is that you can not insert NULL values into a primary key constraint enabled column, whereas you can insert NULL values into a unique key constraint enabled column.