By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

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

P: n/a
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!

Apr 3 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a
In article <11**********************@v46g2000cwv.googlegroups .com>,
su************@gmail.com says...
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.
Apr 3 '06 #2

P: n/a
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

Apr 4 '06 #3

P: n/a
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

Apr 5 '06 #4

P: n/a
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..!

Apr 5 '06 #5

P: n/a
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..!

Apr 5 '06 #6

P: n/a
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..!

Apr 5 '06 #7

P: n/a
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.....??

Apr 5 '06 #8

P: n/a
Ian
technocrat wrote:
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.

Apr 5 '06 #9

P: n/a
In article <44**********@newsfeed.slurp.net>, ia*****@mobileaudio.com
says...
technocrat wrote:
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.


And to help with that, you can find them at http://tinyurl.com/r7sug
Apr 5 '06 #10

P: n/a
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!

Apr 6 '06 #11

P: n/a
the for exception table worked like a charm..Thanks a ton everyone!

Apr 6 '06 #12

P: n/a
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.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@i40g2000cwc.googlegroups. com...
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.....??


Apr 6 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.