sql server programming: Hello,

Continuing with my "Offset" project, I came upon a table with
constraints, namely, a foreign key constraint. I would like to move just
the ID by an offset instead of all the data in the interest of
simplicity. Will the following work?

ALTER TABLE foo DROP CONSTRAINT FKfoo

[...]

ALTER TABLE foo
ADD CONSTRAINT [FKfoo] FOREIGN KEY (
[fooID]
) REFERENCES [bar] (
[barID]
)
-- this is the way the key was created at table creation

Thanks,

Andrew Clark (nospam@nospam.com) writes:
[quoted text, click to view]

Yes, but this will work too:

ALTER TABLE foo NOCHECK CONSTRAINT FKfoo
...
ALTER TABLE foo WITH CHECK CHECK CONSTRAINT FKfoo

This disables and enables the constraint. You may need to check Books Online
for the exact syntax; I'm doing it from memory. And the syntax is such
is really poor...

WITH CHECK CHECK may appears to be nonsense. But the second CHECK
re-enables the constraint. The first WITH CHECK instructs SQL Server
to check that the constraint is valid. The default is not to check,
and thus you may have non-valid data. Furthermore, the optimizer
will not trust a constraint that was reenabled WITH NOCHECK..


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at