A short piece, as I ran into the need recently to alter a column to NOT NULL status. I’ve rarely done this in the past, usually specifying NOT NULL when I create the table. Often in future changes, I’ve been wary of not allowing NULLs since I’ll always find an application, or worse, a business situation where there is no good value available. However that’s a separate discussion.

Altering the Column

Let’s say I have a column that is specified as NULL in a table, and I want to change that. I initially tried this:

ALTER TABLE Tags ALTER COLUMN Status NOT NULL;

However, I got a syntax error. For the life of me, I couldn’t understand why, so I looked up the syntax. If you look at the ALTER TABLE syntax, it shows that the ALTER COLUMN item needs the type included. While I am not changing the data type, to alter the column, I need to do:

ALTER TABLE Tags ALTER COLUMN Status tinyint NOT NULL;

Another inconsistency in SQL. We don’t provide the whole definition again, and here we need to provide the column definition, even when only changing one of the settings.

Filed under: Blog Tagged: syndicated, T-SQL