Is there a way to find the record causing SET INTEGRITY command to fail?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

technocrat - 03 Apr 2006 22:32 GMT

I am trying to laod 2 tables and set integrtiy on them, but the second
tables fails ( both are related and SET INTEGRITY ion first table
succeeds) saying constraint violation....is there a way to find out
which records are violating....?? may be through try catch a
SQLException...??? but how??
any suggestions are welcome!

Reply to this Message

Gert van der Kooij - 03 Apr 2006 22:39 GMT

> I am trying to laod 2 tables and set integrtiy on them, but the second
> tables fails ( both are related and SET INTEGRITY ion first table
> succeeds) saying constraint violation....is there a way to find out
> which records are violating....?? may be through try catch a
> SQLException...??? but how??
> any suggestions are welcome!

Maybe the FOR EXCEPTION option can help you. It removes the duplicates
from the table and puts them in the exception table you specify.

Reply to this Message

technocrat - 05 Apr 2006 21:42 GMT

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...becuase, I am not rteally worried about duplicates...but I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

Reply to this Message

technocrat - 05 Apr 2006 21:42 GMT

Reply to this Message

technocrat - 05 Apr 2006 21:42 GMT

Reply to this Message

technocrat - 05 Apr 2006 21:45 GMT

And another thing,...if i user FOr EXCEPTION....if there are violating
rows....then will the status of the table be "C" at the end of SET
INTEGRITY or "N" ...i mean will it be normal or check pending at the
end of Set Integrity ...for exception.....??

Reply to this Message

Ian - 05 Apr 2006 22:58 GMT

> And another thing,...if i user FOr EXCEPTION....if there are violating
> rows....then will the status of the table be "C" at the end of SET
> INTEGRITY or "N" ...i mean will it be normal or check pending at the
> end of Set Integrity ...for exception.....??

Why don't you read the doc for SET INTEGRITY. It will answer all of
your questions.

Reply to this Message

Gert van der Kooij - 05 Apr 2006 23:09 GMT

> > And another thing,...if i user FOr EXCEPTION....if there are violating
> > rows....then will the status of the table be "C" at the end of SET
[quoted text clipped - 3 lines]
> Why don't you read the doc for SET INTEGRITY. It will answer all of
> your questions.

And to help with that, you can find them at http://tinyurl.com/r7sug

Reply to this Message

technocrat - 06 Apr 2006 15:25 GMT

hey gert and ian...
I did read thse two docs u both mentioned...I did understand some
stuf..but not really clear...i m trying to implement that and see how
it goes....if i hav eany doubts will get back to you guys ...

thanks again!

Reply to this Message

technocrat - 06 Apr 2006 20:40 GMT

the for exception table worked like a charm..Thanks a ton everyone!

Reply to this Message

Pierre Saint-Jacques - 06 Apr 2006 23:50 GMT

AFAIK, the SET INTEGRITY command allows you to insert in the exception table
either or both of FK violations and column check violations.
The command has a parm that allows you to specify ALL, CHECK, FK (or
FOREIgn, can't remember).
So the exception table will contain what you'll specify.
Depending on the option, after the set, the table could still be in check
pending. If you use ALL, after the set, the table will be in normal state
and the status will show it.
Look in the SYSCA.TABLES, two columns, STATUS and CONST_CHECKED.
Your docs. will show you that in CONST_CHECKED:
The first Y is indicates the table has been checked for Foreign Key
integrity.
The second Y is indicates the table has been checked for Column Check
integrity.
The fifth Y is indicates the table has been checked for Refresh for MQT
integrity. (Don't ask Y the fifth as opposed to 3rd, 4th ???? I don't know).
As well don't ask Y the rest of the 32 values, I don't know. Maybe it's
like in the docs: This page intentionnally left blank!!!!

HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

Reply to this Message

ChrisC - 04 Apr 2006 16:05 GMT

I've found this out using SQL in the past. First, you identify which
column(s) the constraint is failing on (it should tell you) and which
foreign table that is referencing (assuming the constraing is a foreign
key).

Then, SET INTEGRITY ... UNCHECKED. Then do a query like:

select * from table1 where col_with_problem not in (select distinct
parent_column from parent_table);

This should get you all of the rows that have the problem. You can
then take remedial action (deleting rows, adding rows into the parent,
or whatever else is appropriate), and then turn integrity off, and
reset the integrity with checking back on.

Note that if the tables are big, this can take a really long time,
though.

-Chris

Reply to this Message

technocrat - 05 Apr 2006 21:28 GMT

you solution looks great and ideal for me...but i aint sure yet ...i ll
have to implement that and try...but in the meanwhile,...can you give
me an example (source code) of how u did that?
Like how u identify which colmns are failing..? and which table its
refering...i know when SET INTEGRITY fails..it throws something..lile
integrity failedfor "schema.table.foreigntableFK" but is ther a way i
can find out the table name...rather than catching the exception and do
"STRING" analysis on it....

Any help would be appreciated...
Thanks

Reply to this Message