Jennifer Lin's Weblog

January 22, 2008

Finding Foreign Key Constraints in Oracle

Filed under: Oracle Dev — jennyca @ 4:36 am

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.

Advertisements

10 Comments »

  1. Wondering if there is a way to find all the references to a given table and its degree of separation. For example.

    table1 -> table2 -> table3

    so table1 references table3 with 2 degree’s of separation (1 if your java)

    Josh

    Comment by Josh — March 13, 2008 @ 9:58 am | Reply

  2. HI
    I AM INCRESING THE SIZE OF PRIMARY KEY COLUMN IN MASTER TABLE, I TRYING TO ALTER THE TABLE, BUT IS SHOWING ERROR ORA – 02267, HOW TO FIND THE REFERENCE COLUMN, I THINK FIRST I HAVE TO INCREASE THE SIZE OF REFERENCE COLUMN IN CHILD TABLE.
    CAN ANY ONE PLEASE HELP ME TO HOW TO THIS THE ERROR ARA – 02267
    I AM USER THIS QUERY [ ALTER TABLE TABLENAME MODIFY (PRIMARY_KEY_COLUMN TYPE);

    Hi PRADEEP,

    If you look at the following link carefully,

    https://jenniferlinca.wordpress.com/2008/01/22/finding-foreign-key-constraints-in-oracle/

    you will find that the solution for your problem is this:

    In addition to changing their data type to the same, adding a Foreign key constraint on a child table is possible, when a PRIMARY/UNIQUE KEY constraint to which the child table refers is enabled on the parent table.

    I’ve tested it. It works.

    Give it a try. We learn by doing.

    Jenny

    Detail steps to do this

    1. connect to sqlplus as the table owner
    2. select constraint_name from user_constraints where constraint_type=’R’ and
    table_name=;
    This will tell you the name of the constraint which you have to use in step 3.
    3. Drop the foreign key constraint:
    alter table drop constraint ;
    4. Modify the column datatype:
    alter table modify ;
    5. Recreate the foreign key constraint. This step works only if the referenced
    column datatype was changed so that it is still compatible with the
    referencing column datatype.
    alter table add constraint foreign key references ();

    Comment by PRADEEP — March 25, 2008 @ 8:18 am | Reply

  3. You save me a lot of time. I was trying to get some tables from the PRODUCTION to the Development in OFSA database, but each time I find number of rows in the Development are more and sometimes not matchings.

    Comment by Mahmoud — June 2, 2008 @ 1:17 pm | Reply

  4. […] they could drop the parent table if the child tables are empty and it did not work. Jeniffer in her page has mentioned on how to go about when the question ‘who is referencing my table?’ comes […]

    Pingback by My confrontations with oracle — June 10, 2008 @ 4:28 am | Reply

  5. […] can find here useful information about how to prepare a script for disabling con before […]

    Pingback by Oracle Export Import Utilities « Oracle Developer’s Weblog — June 27, 2008 @ 10:22 pm | Reply

  6. Hmm… “Many times developers find it difficult to delete records from parent tables when child records reference those tables through foreign key (referential integrity) constraints” because those constraints were implemented in inappropriate fashion: if you are going to ever delete from the parent table, just make FK constraints ON DELETE CASCADE and Oracle will automagically delete child rows along with parent rows in the very same transaction keeping your data consistent. And if you created FKs with default behavior, then you actually agreed that you yourself will make sure there are no child rows before you will attempt to delete a parent row, or Oracle will not allow you to delete it while it has children, again keeping your data consistent. It’s that simple. Your proposed method of going about this by disabling FKs is dangerous at least – as soon as you disable an FK, nothing stops your users from entering invalid data into your database.

    And this: “The parent table can not be truncated, nor deleted, when foreign key(referential integrity) constraints are referencing them.” is only partially correct. You indeed can’t truncate a table that has FKs referencing it regardless if there are any rows in either parent or child table, due to the way TRUNCATE is implemented in Oracle. But the mere fact of an FK being there does not prevent you from deleting from parent table – as long as there are no child rows referencing parent rows being deleted or one of possible ON DELETE options was chosen when the FK constraint was created.

    Comment by Bob — August 14, 2008 @ 12:28 pm | Reply

  7. Thanks! That was very helpful.

    Comment by Frank Kim — March 29, 2011 @ 6:43 pm | Reply

  8. […] further reading please see Finding Foreign Key Constraints in Oracle and Oracle […]

    Pingback by Describing Tables and Constraints in Oracle « betweenGo — March 30, 2011 @ 3:58 pm | Reply

  9. Thanks much! I was in a real hurry and finding this link with the dynamic SQL was a blessing! Thanks!

    Comment by Rob Johnson — May 27, 2011 @ 12:11 pm | Reply

  10. Daily Magazines
    I am full of admiration and positive feelings. Very nice, clean and pleasant. All the best for the author.

    Comment by TheoGretzinger — June 5, 2011 @ 4:15 am | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: