SQL NOT NULL

In this tutorial, we will learn how to use NOT NULL constraint using SQL.

SQL NOT NULL Constraint

• By default, a column can hold NULL values.
• SQL NOT NULL constraint ensures that a column does not accept NULL values.
• If you do not want to allow NULL value in a column, then you have to use a NOT NULL constraint on that column.

SQL NOT NULL constraint on CREATE TABLE Statement

To add a NOT NULL constraint when a table is created, the statement is as follow:

CREATE TABLE Employee(
ID INT NOT NULL,
EmpName VARCHAR(255) NOT NULL,
City VARCHAR(255),
Country VARCHAR(255),
Gender VARCHAR(255),
Salary DECIMAL(18,2)
);

Here Column "ID" and "Empname" does not allow null values when you INSERT new records OR UPDATE the existing records in a table, while "City", "Country", "Gender" and "Salary" allow NULL values.

INSERT INTO Employee(EmpName,City) VALUES('Sourabh','Delhi');

When to execute this statement will result in an error because column "ID" is NULL which violates the NOT NULL constraints specified rule on that column.

SQL NOT NULL constraint using ALTER TABLE - MySQL / Oracle

ALTER TABLE tableName
MODIFY City VARCHAR(255) NOT NULL;

SQL NOT NULL constraint using ALTER TABLE - SQL SERVER

ALTER TABLE Employee
ALTER COLUMN City VARCHAR(255) NOT NULL;

Example

INSERT INTO Employee(ID,EmpName,Country) VALUES(9,'Sourabh','England');

In above, both statements, when to execute this statement will result in an error because column "City" is null which violates the NOT NULL constraints specified rule on that column.