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.
Received on Fri Dec 21 2007 - 10:32:03 CST
