How to Change Column Not to Allow NULL in MS SQL Server

Sometimes you need to set a restriction in a columns of a table which will not allow NULL values in MS SQL Server. To do this, you need to use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE table_name
ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NOT NULL;

In this syntax:

• First, specify the name of the table from which you want to change the column.
• Second, specify the column name with size which will not allow NULL and then write NOT NULL statement.

Let’s create a table for a demonstration:

CREATE TABLE [DataAnalytics].[dbo].[tbl_customer] (
  customer_id long IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(50) NULL,
  email NVARCHAR(30) NOT NULL
);

tbl_customer table has a name column which will allow NULL values. Now you want to change the column name not to allow NULL values. The following statement change the column name not to allow NULL:

ALTER TABLE [DataAnalytics].[dbo].[tbl_customer] 
ALTER COLUMN name NVARCHAR(50) NOT NULL;

Before executing the statement you have to ensure that you don’t have any NULL values in the name column, otherwise it will generate an error message.

I have an another tutorial similar to this tutorial where you will learn how to change a column to allow NULL in MS SQL Server.

In this tutorial, I have shown you how to change a column Name not to allow NULL in SQL Server using ALTER TABLE ALTER COLUMN statement in an existing table. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.