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
>
No comments:
Post a Comment