NOT NULL Constraint

Constraint are rules enforced on columns.

By Default Column can accept Null value, if you don’t want column have Null value 

then you add Null constraint for that column.

Syntax- Column_Name Data_type Not Null.

1. while creating table  

Create Table Emp
(
EmpId Int Identity(1,1),
Name Varchar(50) NOT NULL,
Age Int
)

Insert Into Emp Values ('ABC',24)
Insert Into Emp Values ('PQR',54),('LMN',45)

2. Add not Null Constraint on existing column

Alter Table Emp
Alter Column Age Int Not null---- in this case Age column values should not be NULL
otherwise we have to update old values then we can apply Not Null constarint.

3. Add not Null Constraint on New column

Alter Table Emp
Add Salary Int Not null

error-

Msg 4901, Level 16, State 1, Line 1

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or 

alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'Salary' cannot be added to non-empty table 'Emp' because it does not satisfy these conditions.

Some Rows are already in table, & Null Value for Salary column hence we cannot add Not Null Constraint.

Solution:

1. Default value specify.

Alter Table Emp
Add Salary Int Not null Default(0)

2. Update existing records with some value.

Alter Table Emp
Add Salary Int 

GO

Update Emp Set Salary=10
GO

Alter Table Emp
Alter Column Salary Int Not null

GO

Drop Not Null Constraint

Alter Table Emp
Alter Column Salary Int null

Note- One whole record can accept Null Values for column if they are NOT NULL

CREATE TABLE dbo.Customer

(
Id INT,
Name VARCHAR(100)
)

Insert Into Customer values (1,'ABC')
Insert Into Customer values (Null,Null)

Questions: 

How to add new column with NOT NULL constraint.

assume, Salary is the new column, we have to add,

Alter Table Emp
Add Salary Int Not null Default(0)

How to add NOT NULL constraint to existing column, but existing column have Some NULL values.

assume, Salary is the existing column of the table, 

Update Emp Set Salary=0

Alter Table Emp
Alter Column Salary Int Not null