How to create Not Null Constraint on Column in SQL Server Table - SQL Server / T-SQL Tutorial Part 51

Scenario:

You are creating customer table for one of your requirement, you want to make sure the First name Column always have the value. How will you make sure First Name Column always have the value?

Solution:

Constraints defined rules which make sure the data we insert in column follow the rules.

For above requirement we can create Not Null constraint on a column, Not Null Constraint make sure the value inserted in that column is not Null. If user will try to insert row without providing the value for the column on which we have created the Not Null Constraint , The insert will fail as column can not accept Null values.

To create Not Null Constraint on Column in a Table, we have to Add Not Null after the Data Type as shown below for First Name Column.

Create Table Customer
(FirstName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)
)

You can see that I did not provide Not Null Constraint for other columns, that means those column can accept Null values or Unknown value.

Let's insert below rows and see what happen

insert into dbo.Customer ( FirstName,LastName,Age,PhoneNumber,DOB,Gender)
Values
('Aamir','Shahzad',Null,Null,Null,Null)
,(Null,'Raza',33,'00000000',Null,'M')

As you can see that the transaction failed as we can't insert the Null in First Name column, No Record will be inserted.

How to create Not Null Constraint on a column in SQL Server - TSQL Tutorial 

Let's consider second Insert , Where we are not inserting anything in First Name column. Still the insert will fail, as when you don't provide the column in insert list, it try to insert Null value for that column and we have created Not Null Constraint on First Name column.

insert into dbo.Customer (LastName,Age,PhoneNumber,DOB,Gender)
Values
('Raza',33,'00000000','2016-01-26','M')

How to create Not Null Constraint in SQL Server - SQL Server / TSQL Tutorial