有没有办法找到导致SET INTEGRITY命令失败的记录？ [英] Is there a way to find the record causing SET INTEGRITY command to fail?

问题描述

我试图铺设2张桌子并设置完整性，但第二张

表失败（两者都是相关的，SET INTEGRITY离子第一张表

成功）说约束违规....有没有办法找出

哪些记录违反了......？可能是通过尝试抓住一个

SQLException ...... ???但是怎么样？

欢迎任何建议！

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!

推荐答案

文章< 11 *** *******************@v46g2000cwv.googlegroups .com> ;,
su ************ @ gmail.com 说...

In article <11**********************@v46g2000cwv.googlegroups .com>,
su************@gmail.com says...

我试图铺设2张桌子并设置完整性在他们身上，但第二个表失败（两者都是相关的并且SET INTEGRITY离子第一个表成功）说约束违规....有没有办法找出哪些记录违反了....？可能是通过尝试捕获一个
SQLException ... ???但是如何？
欢迎任何建议！

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!

也许FOR EXCEPTION选项可以帮到你。它从表中删除重复的

并将它们放在你指定的异常表中。

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

我发现这是在SQL中使用SQL过去。首先，您确定约束失败的哪个
列（它应该告诉您）以及哪个

引用的外表（假设约束是外来的

key）。


然后，SET INTEGRITY ... UNCHECKED。然后进行如下查询：


select * from table1，其中col_with_problem不在（选择不同的

来自parent_table的parent_column）;


这应该可以解决所有有问题的行。您可以

然后采取补救措施（删除行，在父级中添加行，

重新检查完整性。


请注意，如果表格很大，这可能需要很长时间，但是b $ b。


-Chris

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

你的解决方案看起来很棒，对我来说很理想...但我还不确定。 ..i ll

必须实现并尝试...但同时，...你能给我一个例子（源代码）你怎么样？那样做了吗？

就像你如何确定哪些colmns失败了？？哪个表格它引用了......我知道当SET INTEGRITY失败时......它会抛出一些东西...... lile

完整性失败schema.table.foreigntableFK但是有一种方式我可以找到表名...而不是捕捉异常并做

STRING分析....


任何帮助将不胜感激...

谢谢

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

这篇关于有没有办法找到导致SET INTEGRITY命令失败的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！