SQL SERVER – How to Change Column Property From NULL to NOT NULL Value?

A Very popular question I often here whenever I am presenting at the developer conference – How to Change Column Property From NULL to Not NULL Value?

Well, the answer is very simple, however, let us understand that with a simple example.

First, we will create a table with a column which is NULL. In this example, we will create two different columns, one with the datatype INT and the second with the datatype VARCHAR.

CREATE TABLE TestTable (ID INT, Col VARCHAR(100));
INSERT INTO TestTable (ID, Col)
SELECT NULL, NULL
UNION ALL
SELECT 1, NULL
UNION ALL
SELECT NULL, 'Val'
GO

Now we will try to change that column to NOT NULL. However, whenever we execute the following script it will give us an error.

ALTER TABLE TestTable
ALTER COLUMN ID INT NOT NULL;
ALTER TABLE TestTable
ALTER COLUMN Col INT NOT NULL;
GO

Whenever we try to change the column datatype from NULL to NOT NULL, it is important that the column is populated with some value. If the column has a NULL value and we attempt to make it NOT NULL, it will give us an error.

Msg 515, Level 16, State 2, Line 10
Cannot insert the value NULL into column ‘ID’, table ‘tempdb.dbo.TestTable’; column does not allow nulls. UPDATE fails.

So first we will populate some value into our columns. In this example, we will put value 0 (zero) in INT column and ” (empty string) in VARCHAR column.

UPDATE TestTable
SET ID = 0, Col = ''
GO

Next, we will execute the same command can convert the column from NULL to NOT NULL.

ALTER TABLE TestTable
ALTER COLUMN ID INT NOT NULL;
ALTER TABLE TestTable
ALTER COLUMN Col INT NOT NULL;
GO

Let me know if you have any better script to achieve the same task.

Reference: Pinal Dave (https://blog.sqlauthority.com)