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