Say I have the following table:
CREATE TABLE test(test_id int not null identity primary key, field1 int not null, field2 int ); CREATE INDEX IDX_test_field1 ON test(field1); CREATE INDEX IDX_test_field2 ON test(field2);
ALTER TABLE test ALTER COLUMN field1 int works, and
Nevertheless, I cannot change
ALTER TABLE test ALTER COLUMN field2 int not null because of
ALTER TABLE ALTER COLUMN field2 failed because one or more objects access this column.
Also, I cannot change
field1 back to
However, I can add and then drop check constraint as many times as I want :
ALTER TABLE test ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL); DROP CONSTRAINT CHK_NN_field2` without any problems.
Is it well defined behaviour? Could someone explain why that happens or point me to documentation?
I'm using SQL Server 2008 R2 if that matters.