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: <>
Date: Fri, 21 Dec 2007 08:32:03 -0800 (PST)
Message-ID: <>

On Dec 20, 3:46 pm, Mark D Powell <> wrote:
> 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 --- Hide quoted text -
> - Show quoted text -

According to what I've just seen, it doesn't appear that it's the 'VALIDATED' column in dba_constraints is as important as the Delete Rule column having a value of 'NO ACTION'. I've already found dozens of instances where the column was validated but since it had a delete rule of NO ACTION, there were foreign key violations in the data. Received on Fri Dec 21 2007 - 10:32:03 CST

Original text of this message