Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Foreign key integrity - is there a way to verify it programmatically?

Re: Foreign key integrity - is there a way to verify it programmatically?

From: Mark D Powell <>
Date: Thu, 20 Dec 2007 15:46:50 -0800 (PST)
Message-ID: <>

On Dec 20, 5:05 pm, "" <> wrote:
> Here's my situation - I'm working on a large scale data cleanup
> project and part of my duties include finding and fixing data
> anomolies such as date errors (someone entered 2070 for the year
> instead of 2007) or values that are out of range.
> The other thing I'm on the lookout for is tables that *probably*
> should have a foreign key relationship but for whatever reason do
> not. Fortunately the data administration group here is pretty good as
> most of the tables do in fact have the correct keys in place. The
> other day however I stumbled onto a problem that is of great concern
> to me. In plain English, here's what I hope is an accurate and
> understable example of the problem:
> I have an employee table with a numeric field called ee_id as it's
> primary key. The rest of the columns in the table are standard stuff
> that you'd normally have in a table like this. Throughout the rest of
> the database are dozens of other tables that contain an ee_id column
> and have a foreign key relationship established so that if someone
> tries to delete an employee but that ee_id exists in another table
> somewhere, the database will disallow it.
> Strictly by accident I ran the following query on a table that does
> have a foreign key but still got rows returned anyway:
> select * from eblue where ee_id not in (select ee_id from
> employee);
> Given that the foreign key is there, I shouldn't have gotten any rows
> back but unfortunately did. This brings me to the question I need an
> answer for - Is there any way of validating all of the foreign keys in
> the database to see if there are other anomolies like this?
> Although I could write SQL script to do it, since there are over 500
> tables I was hoping for an automated solution if one exists.
> Thanks for reading,
> Christopher

Oracle supports adding FK, UK, and PK constraints to table even if the constraint rule is broken by some of the data. This allows you to define the rule to prevent any future violations without first having to clean the data. This can be handy if you have to import/support legacy data. You may be looking at such a case.

Check the definition of the constraint via dba_constraints.validate and see if it says novalidate. This means it was created without requiring validation.

You might want to talk to someone who knows the data, how it is used, and maybe how it was created. It might be possible to repair the rows or delete them depending on what you find out.

You should not need to validate the data referenced by FK that is marked as Validated in dba_constraints.

HTH -- Mark D Powell -- Received on Thu Dec 20 2007 - 17:46:50 CST

Original text of this message