SQL NOT NULL Constraint Explained [Easy Examples]

Overview of SQL NOT NULL constraint

The SQL NOT NULL constraint on an attribute or column specifies that the NULL value is not allowed for that attribute, in other word, the constraint in SQL excludes the null value from the domain of that column values.

If value for in particular column is not specified than by default it hold NULL, it mean to preventing from NULL value , we need to force user to enter that in column or in attribute for that we need to give SQL NOT NULLL constraint at the time of table creation with SQL Create table statement

 

SQL Constraints

SQL constraints are a set of rules implemented on tables in relational databases to dictate what data can be inserted, updated or deleted in its tables. This is done to ensure the accuracy and the reliability of information stored in the table.

The purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.  Once the constraint is placed, if any operation in the database does not follow the rules specified by the constraint, the particular operation is aborted.

 

Types of SQL Constraints

SQL constraints can be at a column or a table level. Following is a list of the most commonly used column level SQL constraints:

• NOT NULL Constraint
• UNIQUE Constraint
• DEFAULT Constraint
• CHECK Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

 

The SQL NOT NULL Constraint

A NOT NULL constraint specifies that cell value for any row for this column cannot be blank. All table updates must specify values in this column which having this constraint. SQL NOT NULL constraint can be specified on columns when you create a table, or set the constraint on an existing table with ALTER TABLE.

 

How to Apply SQL NOT NULL Constraint with Create table

Applying a NOT NULL Constraint during the Creation of a Table Syntax:

CREATE TABLE <table_name> (
    Column_name1 datatype  NOT NULL,
    Column_name2 datatype  NOT NULL,
    
);

By specifying the words NOT NULL after the column definition, we create a table with column name specified where the columns cannot be blank.

 

Altering a Table after creation using Alter Table Statement

After creating a table and adding records into it, if we need to add a constraint on column, the Alter table query is used,

Applying a NOT NULL Constraint after creating a Table using Alter table Syntax:

SQL Server

ALTER TABLE <table_name>
ALTER Column Column_name datatype NOT NULL

 

Oracle 10G & later

ALTER TABLE <table_name>
MODIFY Column_name datatype NOT NULL

 

MySQL

ALTER TABLE <table_name>
MODIFY ColumnColumn_name datatype NOT NULL

 

SQL NOT NULL Constraint Examples

SQL NOT NULL Constraint with create table

CREATE TABLE [dbo].[tblemp](
    [Emp_id]   INT NOTNULL,
    [Emp_name]   VARCHAR (50) NOTNULL,
    [street]   VARCHAR (50) NULL,
    [city]   VARCHAR (50) NULL,
    [Emp_contact] VARCHAR (50) NULL,
    [Salary]   INT NULL,
    [Dept_id]   INT NULL,
CONSTRAINT [PK_tblemp] PRIMARYKEY CLUSTERED ([Emp_id] ASC)
);

In above Create table query, by applying NOT NULL Constraint on column emp_id and emp_name which cannot be blank while inserting records in table, where as other columns like street, city, contact can have null values

If we inserting records in above created table

INSERT INTO tblemp(Emp_id,Emp_name,street,city,Emp_contact,Salary,Dept_id) 
VALUES (111,'rita','ramjan','mumbai','90909090',55000,10)

After executing above query one record is inserted into tblemp table, we have specified all the value for each column the row has added successfully

If we do not specified value for emp_name column, which has constraint of NOT NULL

INSERT INTO tblemp(Emp_id,Emp_name,street,city,Emp_contact,Salary,Dept_id) 
VALUES (111,'rita','ramjan','mumbai','90909090',55000,10)

In above query we have specified value for each column except one emp_name which has constraint of NOT NULL, it returns error message in SQL Server of ‘Cannot insert the value NULL into column 'Emp_name'

OUTPUT:

Likewise, when we did not specified value for another column of emp_id , it will return same error

INSERT INTO tblemp(Emp_name,street,city,Emp_contact,Salary,Dept_id) 
VALUES ('rita','ramjan','mumbai','90909090',55000,10)

OUTPUT:

If we do not specify column value for and allow null column like street and city, it will insert successfully as give below

INSERT INTO tblemp(Emp_id,Emp_name,street,Emp_contact,Salary,Dept_id) 
VALUES (116,'shiva','meera road','36363636',50000,20)

In previous query, we didn’t specify the city column value, but still the row has inserted successfully, because city column value can be null

OUTPUT:

 

SQL NOT NULL Constraint with Alter table example

Modifying column city applying NOT NULL constraint,

Alter table tblemp
Alter column city varchar(20) NOTNULL;

• When we execute above query it shows one error message as ‘column does not allow nulls. UPDATE fails.’
• As column city is already having one NULL value in cell so, before altering column and applying SQL NOT NULL constraint, we need to remove that the record, or insert values were having NULL value for that column
• Once we inserted value in that column, the above query executed successfully

OUTPUT:

 

Remove SQL NOT NULL constraint

As we have seen in previous section to modify column structure apply SQL NOT NULL Constraint SQL Alter table is used, likewise to remove SQL NOT NULL Constraint alter table query is use,

Example of removing SQL NOT NULL Constraint

In previous example we have use employee table in which SQL NOT NULL Constraint has applied on column emp_name, so to remove this SQL alter table statement is used

Alter table tblemp 
Alter column Emp_name varchar(20) NULL

• Above SQL query executes successfully on SQL Server
• while removing SQL NOT NULL constraint we do not need to check for having data in that particular column or not

 

Difference between SQL NOT NULL Constraint and SQL Unique Constraint

• SQL NOT NULL constraint prevents a database value from being null. A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null
• with SQL NOT NULL constraint we can give same value in the same column multiple times, whereas using SQL Unique constraint we cannot repeat the same value in same column

 

SQL NOT NULL Constraint from SQL Server design view

In SQL server we can other option to apply SQL NOT NULL Constraint by just right-clicking on the table on which we want to apply constraint and choose Design option or Open Table Definition, next to each column name there is a checkbox to specify allow Null or not, if we do not want to allow NULL value we just need to uncheck it will create SQL NOT NULL constraint automatically,

 

Summary

In this Article of SQL Not Null, we have first covered what is SQL Constraints, Types of SQL Constraint, Overview of SQL Not Null constraint, SQL Not Null constraint with Create table statement, SQL Not Null Constraint with Alter table with practical examples, also covered various syntax to write Alter table query on different database servers, in last section of this article we have covered difference between SQL Not Null constraint with SQL Unique constraint

 

References

SQL Create Table statement
SQL null value

 

Further Reading

SQL Constraints