Not Null Constraint in SQL Server

NOT Null CONSTRAINT

- It is not allowed to insert null values in the column.

Set Not null constraint to column using Creating Table statement

Create Table customer (id Int Primary Key,name Varchar(50) Not null,emailID varchar(50) Not Null,mobileNum varchar(50))

insert into customer values(1,'Chaitanya Patil','[email protected]')

If We try to insert a null value in the column then we will get an error.

insert into customer values(1,'Chaitanya Patil',null)//Cannot insert the value NULL into column 'emailID', table 'pratikshadb.dbo.customer'; column does not allow nulls. INSERT fails.

insert into customer (id,emailID) values(3,'[email protected]')//Cannot insert the value NULL into column 'name', table 'pratikshadb.dbo.customer'; column does not allow nulls. INSERT fails.

It will allow to insert blank value if data type is varchar,varchar2 etc

insert into customer values(2,'Sakshi Patil','')

select * from customer

Set Not null constraint to column using Alter statement after table creating

Here we have a mobile number column that allows us to insert null value. but now we want the mobile number to not accept the null value.

If we check the table record already we see there is a null value present in the mobile_no column.

so first we need to remove the null value from column and set the default value for it

update customer set mobile_no = '' where mobile_no is null

select * from customer

if the data type is int then we need to set the default value as a 0.

Now we add not null constraint using alter command

alter table customer alter column mobile_no varchar(50) not null

Removing Not Null Constraint

- alter table customer alter column mobile_no varchar(50)  null