Check Foreign Key integrity of existing data

20 Oct 2005 5:15 PM

ESPNSTI

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

20 Oct 2005 6:45 PM

Jerry Spivey

> 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
Show quote

"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
>
>

20 Oct 2005 8:06 PM

ESPNSTI

OK, so manually run a check.
Didn't even think of that. :)
Thanks!

Show quote

"Jerry Spivey" <jspi***@vestas-awt.com> wrote in message
news:ufro3Za1FHA.1040@TK2MSFTNGP14.phx.gbl...
> > 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

21 Oct 2005 6:53 AM

Tibor Karaszi

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/

Show quote

"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" <jspi***@vestas-awt.com> wrote in message
> news:ufro3Za1FHA.1040@TK2MSFTNGP14.phx.gbl...
>> > 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
>
>