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

On Dec 21, 11:32 am, "syclon..._at_hotmail.com" <syclon..._at_hotmail.com> wrote:
> On Dec 20, 3:46 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Dec 20, 5:05 pm, "syclon..._at_hotmail.com" <syclon..._at_hotmail.com>
> > 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.- Hide quoted text -
>
> - Show quoted text -

You seem to be misunderstanding the meaning of "no action"

From the Concepts manual (10gR2) >>
Delete No Action
The No Action (default) option specifies that referenced key values cannot be updated or deleted if the resulting data would violate a referential integrity constraint. For example, if a primary key value is referenced by a value in the foreign key, then the referenced primary key value cannot be deleted because of the dependent data. <<

Verify that the constraints in question are enabled.

HTH -- Mark D Powell -- Received on Sat Dec 22 2007 - 09:09:34 CST