How to alter a column from null to not null in sql server

Changing a column from NULL to NOT NULL consist in making a change on the table by using ALTER TABLE syntax.


There are few steps that you have to follow in order to change a column from null to not null.

  1. Check the column to see if there is any NULL value, if you find one or more proceed to change it for a default value, (on INT columns it is common to use 0)
  2. After change is done proceed to make the change on the column by using ALTER TABLE TableName ALTER COLUMN ColumnName Datatype NOT NULL;
  3. Verify that change worked as expected.

For working on the following examples we are going to use the following dataset.

1. Dataset

On this example we are going a create a table and call it users, then fill it with some demo data.

--Creating demo table
Create Table users(
	username varchar(250)
   ,age int NULL
)
--Inserting demo data into the table
INSERT INTO users
VALUES   ('User1',NULL)
		,('User2',25)
		,('User3',22)
		,('User4',NULL)
		,('User5',32)

As you can see the created table has a field called ‘age’ that allows NULL values. also we inserted some NULL values into this column.

2. How to check if columns does contain NULL values

The first step is to confirm that change target column does not contain any NULL value, for that you can use ISNULL function into the WHERE clause.

Example

SELECT * 
FROM users 
WHERE age IS NULL

Results

Then we need to Update NULL values that we found in the column with default values (something that you pick, It depends on what kind of data do you have)

Example

UPDATE users
SET age = 0
WHERE age IS NULL

On this update we maintained the structure of the SELECT statement that we used for discovering the NULL values table had and converted it into an UPDATE statement.

Results

As you can see, all records that contained NULL where updated.

Default value used for updating the table depends on column datatype and your criteria.

3. How to change a column from NULL to NOT NULL using ALTER TABLE

After changing the NULL values that we had on the table, we are going to proceed to change the table structure.

Example

ALTER TABLE users
ALTER COLUMN age VARCHAR(250) NOT NULL;

Results

If you didn’t change NULL values that were on the table you are going to receive an error message.


Leave a Reply

Your email address will not be published. Required fields are marked *