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

On Dec 20, 5:05 pm, "syclon... (AT) hotmail (DOT) com" <syclon... (AT) hotmail (DOT) com>
wrote:
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 --

On Dec 20, 3:46*pm, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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.

On Dec 21, 11:32*am, "syclon... (AT) hotmail (DOT) com" <syclon... (AT) hotmail (DOT) com>
wrote:
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 --