SQL - NOT NULL

By default, a column of a table holds NULL values. The SQL NOT NULL constraint is used to enforce a column to NOT accept NULL values. This ensures the column to always have a value. This implies that a new record can not be inserted without providing a value to this column. Similarly, this column can not be updated with a NULL value.

SQL NOT NULL constraint with CREATE TABLE

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which NOT NULL constraint is applied on columns EmpID, Name and City.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100) NOT NULL,
  Age INT,
  Salary DECIMAL(18,2)
);

SQL NOT NULL constraint with ALTER TABLE

In the above example, a table called Employee is created in which column Salary does not have NOT NULL constraint. To enforce NOT NULL constraint on this column, the SQL code is given below:

MySQL / Oracle (prior 10G version)

ALTER TABLE Employee
MODIFY COLUMN Salary DECIMAL(18,2) NOT NULL;

Oracle (10G and later)

ALTER TABLE Employee
MODIFY Salary DECIMAL(18,2) NOT NULL;

SQL Server / MS Access

ALTER TABLE Employee
ALTER COLUMN Salary DECIMAL(18,2) NOT NULL;

Remove NOT NULL constraint

To drop the NOT NULL constraint from column City of above mentioned Employee table, the SQL code is given below:

MySQL / Oracle (prior 10G version)

ALTER TABLE Employee
MODIFY COLUMN City VARCHAR(100) NULL;

Oracle (10G and later)

ALTER TABLE Employee
MODIFY City VARCHAR(100) NULL;

SQL Server / MS Access

ALTER TABLE Employee
ALTER COLUMN City VARCHAR(100) NULL;