SQL Server - NOT NULL Keyword

By default, a column of a table holds NULL values. The SQL Server (Transact-SQL) NOT NULL keyword which is a constraint and it is used to enforce a column to NOT accept NULL values. This ensures the column to always have a value. This implies that a new record can not be inserted without providing a value to this column. Similarly, this column can not be updated with a NULL value.

SQL Server NOT NULL constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which NOT NULL constraint is applied on columns EmpID, Name and City.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100) NOT NULL,
  Age INT,
  Salary DECIMAL(18,2)
);

SQL Server NOT NULL constraint with ALTER TABLE

In the above example, a table called Employee is created in which column Salary does not have NOT NULL constraint. To enforce NOT NULL constraint on this column, the following statement can be used:

ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(18,2) NOT NULL;

Remove NOT NULL constraint

To drop the NOT NULL constraint from column City of above mentioned Employee table, the statement is given below:

ALTER TABLE Employee
ALTER COLUMN City VARCHAR(100) NULL;