Altering a column to add NOT NULL

I had to do this recently and needed to look up the syntax as I couldn’t remember it. I don’t do this often, but I was working on a particular item and wanted to test this.

I had a table, something like this:

CREATE TABLE SimpleTable ( MyID INT NOT NULL PRIMARY KEY , MyChar VARCHAR(200) , [Status] TINYINT );

I decide that the [Status] column shouldn’t be NULL and decide to ensure it’s always got a value. I don’t want to rebuild the table, I want to ALTER it. I use this syntax:

ALTER TABLE dbo.SimpleTable ALTER COLUMN Status TINYINT NOT NULL;

That’s it. I do this and the column now no longer accepts NULL values.

Of course, if there’s data in there, I need to set those values to something that isn’t NULL before I can do this, but that’s a separate topic.

By the way, I remembered most of this syntax. What I forgot is that I need to include the datatype in there to make this change. There are a number of restrictions with this command, so be sure that you read carefully before you try to make changes. If you do this in SSMS, you might end up with a script for a new table, which may not be what you want to occur in a production environment.

Reference

ALTER TABLE – https://msdn.microsoft.com/en-us/library/ms190273.aspx

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Altering a column to add NOT NULL

  1. Ron Kyle says:

    It’s pretty straight forward to update the existing NULL values to a default. In this case

    ALTER TABLE… ALTER COLUMN Status TINYINT NOT NULL CONSTRAINT DF_ DEFAULT (0) or (1).

Comments are closed.