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.