Check integrity

Hi,

What methods can we use to check the integrity problems? Some records in my
database are having Foreign Keys but the database doesn't have any related
records with required Primary Keys. I need to scan the whole database and
delete (maybe with backup, maybe not) all wrong records.

Who can I do that? Is it need to write my own application to do that or we
have some standard way to fix these problems? I'm not a database
administrator and don't know these ways (yet). Can somebody help me with
advice?

Thanks.

Dmitri ShvetsovHi

If you have a foreign key constraint enabled for the given relationships
then this should not occur.
If they were created with the NOCHECK option then existing data may violate
the constraint, but if you would not be able to create the FK if you this
was the case and NOCHECK was not specified.

The easiest way to check the integrity is probably to drop and reapply the
FKs. Another alternative would be to use a construct such as

SELECT * FROM Referencing C WHERE NOT EXISTS ( SELECT * FROM Referenced P
WHERE P.PK = C.FK )

But doing this for a large database may take some time.

John

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov|||Hi

If you have a foreign key constraint enabled for the given relationships
then this should not occur.
If they were created with the NOCHECK option then existing data may violate
the constraint, but if you would not be able to create the FK if you this
was the case and NOCHECK was not specified.

The easiest way to check the integrity is probably to drop and reapply the
FKs. Another alternative would be to use a construct such as

SELECT * FROM Referencing C WHERE NOT EXISTS ( SELECT * FROM Referenced P
WHERE P.PK = C.FK )

But doing this for a large database may take some time.

John

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov|||Try using dbcc checkconstraints with all_constraints . It should return all
'bad' data in the database...

MC

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov
>|||Try using dbcc checkconstraints with all_constraints . It should return all
'bad' data in the database...

MC

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov
>

Check Foreign Key integrity of existing data

Hi,
I'm in the process of writing a script that inserts or updates default data
for a database.
I came to the conclusion that I have to temporarily disable certain foreign
keys.
At the end of the script however, I'd like to check existing data to verify
that everything is still ok.
Basically something like this:
ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
-- Insert data here
ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
existing data, right?
-- Process other tables here
-- At this point I'd like to check that the FK_MyForeignKey constaint is
valid for existing data
Now I know that I could do something like this:
ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
However, is there a way to check existing data without dropping and
re-adding the constraint?
Or is dropping and re-adding not that costly?
Thanks,
Erik> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
Right.

> However, is there a way to check existing data without dropping and
> re-adding the constraint?
Use an IF EXISTS (or similar query)...test prior to running the ALTER
TABLE...CHECK statement.
HTH
Jerry
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message
news:eGZJSnZ1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm in the process of writing a script that inserts or updates default
> data
> for a database.
> I came to the conclusion that I have to temporarily disable certain
> foreign
> keys.
> At the end of the script however, I'd like to check existing data to
> verify
> that everything is still ok.
> Basically something like this:
> ALTER TABLE MyTable NOCHECK CONSTRAINT FK_MyForeignKey
> -- Insert data here
> ALTER TABLE MyTable CHECK CONSTRAINT FK_MyForeignKey -- This doesn't check
> existing data, right?
> -- Process other tables here
> -- At this point I'd like to check that the FK_MyForeignKey constaint is
> valid for existing data
>
> Now I know that I could do something like this:
> ALTER TABLE MyTable DROP CONSTRAINT MyForeignKey
> ALTER TABLE MyTable WITH CHECK ADD CONSTRAINT MyForeignKey ...
>
> However, is there a way to check existing data without dropping and
> re-adding the constraint?
> Or is dropping and re-adding not that costly?
> Thanks,
> Erik
>|||OK, so manually run a check.
Didn't even think of that. :)
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
check
> Right.
>
> Use an IF EXISTS (or similar query)...test prior to running the ALTER
> TABLE...CHECK statement.
> HTH
> Jerry|||You can also check out DBCC CHECKCONSTRAINTS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ESPNSTI" <ESPNSTISPAM@.Hotmail.com> wrote in message news:OVnT3Gb1FHA.1212@.TK2MSFTNGP10.phx
.gbl...
> OK, so manually run a check.
> Didn't even think of that. :)
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ufro3Za1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> check
>sql

Check constraint and foreign key error

The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-Unfortunately there's no system variable that gives you the actual error
message, so that you could parse it for more information. But from a client
application, you should be able to access the error message. For example,
Err.Description in ADO.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
The error message for err. 547 is:
"%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
This error is raised when a foreign key error is occured, and when a check
constraint error occured, too.
What kind of values can have the parameters of this error message? How can I
know, what kind of error is this?
thanks
-enci-|||I have the error message, but I want to format and translate it. And I didn'
t
know what kind of values can have the messages parameters. I have diferent
messages and todo's in case of check constraint and diferent in case of
foreign keys. And I didn't know what kind of any errors can appear with this
error number.
thanks
-enci-
"Narayana Vyas Kondreddi" wrote:

> Unfortunately there's no system variable that gives you the actual error
> message, so that you could parse it for more information. But from a clien
t
> application, you should be able to access the error message. For example,
> Err.Description in ADO.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eniko Tegen" <EnikoTegen@.discussions.microsoft.com> wrote in message
> news:04C8D5D7-D273-4D68-9757-31DF9ED09867@.microsoft.com...
> The error message for err. 547 is:
> "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict
> occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."
> This error is raised when a foreign key error is occured, and when a check
> constraint error occured, too.
> What kind of values can have the parameters of this error message? How can
I
> know, what kind of error is this?
> thanks
> -enci-
>
>