6

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.

Improve this question
2
  • @JNK: No views at all. Empty database. DDL I posted is just an example that illustrates the problem; you can run on any sqlserver 2008 r2 and get the same results.
    – a1ex07
    Oct 19, 2012 at 16:35
  • Sorry guys, speed-reading fail!
    – JNK
    Oct 19, 2012 at 16:38

3 Answers 3

Reset to default
4

The relevant documentation is here

ALTER COLUMN Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

  • Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

  • Used in statistics generated by the CREATE STATISTICS statement unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or greater than the old size, or if the column is changed from not null to null. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.

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.

CREATE TABLE test2(pk varchar(10) primary key);

ALTER TABLE test2 ALTER COLUMN pk varchar(100) NOT NULL
Improve this answer
4
  • Thank you for explanation, that makes sense for me. However, I guess your latest example is not absolutely correct - if you specify column as a primary key sqlserver makes it not null implicitly, so following alter column does nothing... The same behaviour with indexed column ; for table in my question I can issue ALTER TABLE test ALTER COLUMN field1 int not null without any errors because it's already not null...
    – a1ex07
    Oct 20, 2012 at 12:17
  • @a1ex07 the second example changes the length.
    – Martin Smith
    Oct 20, 2012 at 12:52
  • sorry, I missed that :)
    – a1ex07
    Oct 20, 2012 at 14:17
  • For future reference, changing collation is also not allowed when an index/constraint exists - this just bit me where a database's collation was case-insensitive, but the column was case-sensitive and the alter statement didn't specify and preserve the collation difference (i.e. the alter statement left out the collate clause).
    – Jarrod Dixon
    Sep 6, 2017 at 19:08
0

Your indexes on field1 and field2 are preventing you from changing the columns from NULL to NOT NULL. Drop the index, change it to NOT NULL and then recreate the index.

Improve this answer
2
  • I know what happens, but I'm asking why that happens. Also, changing not null to null column works, but not vice versa.
    – a1ex07
    Oct 19, 2012 at 18:22
  • I would guess that going from NOT NULL to NULL works because every value stored in a column defined as NOT NULL is allowed if the definition is changed to NULL. The opposite is not true. Not every value stored in a nullable column will be allowed in a non-nullable column.
    – Eli
    Oct 19, 2012 at 20:18
0

NULL is a looser restriction than NOT NULL.

What would happen if you had NULL fields when you try to change to NOT NULL (constraint violation and fail), vs. when you have NOT NULL, changing to NULL (no impact).

When you create a constraint, the data must be validated to conform to that constraint prior to the table alteration (or it fails) - from what I've observed this behavior doesn't occur on alter or drop & instead changing constraints in this way is simply prevented.

Improve this answer
2
  • But the same works with check constraint ALTER TABLE test ADD CONSTRAINT CHK_NN_field2 CHECK (field2 IS NOT NULL);
    – a1ex07
    Oct 19, 2012 at 19:51
  • Key word: ADD -- you're creating the new constraint which triggers data validation. You're not altering a column which has the constraint applied against it.
    – iivel
    Oct 19, 2012 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.