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);
Now ALTER TABLE test ALTER COLUMN field1 int
works, and field1
allows null
.
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 not null
.
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.
Thank you.
Best Answer
The relevant documentation is here
In practice it seems that SQL Server does allow some additional cases beyond that mentioned in the documentation however.
As you show in your question
ALTER TABLE test ALTER COLUMN field1 int null
does in fact work so the restrictions on changes to columns used in indexes appear to be the same as those for user created statistics.Moreover the caveat mentioned about Primary Keys seems to be untrue also. The following works fine.