473,287 Members | 1,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

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
12 2358
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
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
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
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
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
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
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
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
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
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
the for exception table worked like a charm..Thanks a ton everyone!

Apr 6 '06 #12
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: maricel | last post by:
I have the following base table structure - DDL: CREATE TABLE "ADMINISTRATOR"."T1" ( "C1" INTEGER NOT NULL ) IN "TEST_TS" ; ALTER TABLE "ADMINISTRATOR"."T1" ADD PRIMARY KEY
15
by: Steve | last post by:
I have a form with about 25 fields. In the BeforeUpdate event of the form, I have code that sets the default value of each field to its current value. For a new record, I can put the focus in any...
1
by: ms | last post by:
I am running an insert statement from a dbf file and there is one record causing the insert to fail. A msg. is returned stating it is due to an invalid datatype. There are 2 text fields, 2...
1
by: Stephan Bour | last post by:
Hi, I am trying to implement a SQLServer database update inside a catch loop and I'm having difficulties pointing to the row to update using the current order's OrderID primary key Identifier in...
16
by: Brian Tkatch | last post by:
Is there a way to check the order in which SET INTEGRITY needs to be applied? This would be for a script with a dynamic list of TABLEs. B.
5
by: prakashwadhwani | last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in my form were working perfectly. However, after I added a call to the "Save_Fields_In_Form_Header" Event/Proc in the...
7
by: Neil | last post by:
Was working in A2003 and noticed that the Save Record item on the Records menu was not available when the record was not dirty. In A2000, Save Record was always available. (This is a problem for me...
2
by: ApexData | last post by:
Access2000, using a continuous form. I’m getting a message that say “you cannot add or change a record because a related record is required in table Employee”. This occurs in all my combobox...
22
by: paul | last post by:
A crude, unlikely scenario just so I can get my head around this: Split DB; front end back end. Each user has the FE locally the BE is stored on a server. 1. UserA opens customerA's record and...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.