Bug: "ALTER TABLE *** WITH NOCHECK" does not work for SQL Server 2005

I wanted to turn off 'Enforce Foreign Key Constrain' using SQL. However, it did not work on the SQL Server 2005 database.

For example, running following SQL in Management Studio, and the result showed that 'Enforce Foreign Key Constrain' property for the newly created constraint was still enabled.

ALTER TABLE [dbo].[Tags] WITH NOCHECK ADD CONSTRAINT [FK_Tags_ChannelID] FOREIGN KEY([ChannelID]) REFERENCES [dbo].[Channels] ([ID])

Does anybody know any way to get around with this problem. It is quite important for my software.

Ps. with the SP1, problem remains the same.

ADD CONSTRAINT WITH NOCHECK is for not to fire the constraint against existing data in the table, when the constraint is created. If you want to disable the constraint you will need to run this statement as well:

ALTER TABLE dbo.Tags NOCHECK CONSTRAINT FK_Tags_ChannelID

"WITH NOCHECK" and "NOCHECK" have different meanings here even though they look similar.

|||

Thanks, That works very well.

What surprised me is that when I tried to generate the SQL script for a table with the disabled constraint using Management Tools, it did not give the other SQL statement. That is why I thought it is a bug of SQL Server 2005. Well, it IS a bug for the management tool then.

Thanks for the help