SQL NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "id", "Last_name", and "First_name" columns will NOT accept NULL values when the "student" table is created:

Example

 CREATE TABLE  student(
id int  NOT NULL ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) NOT NULL ,
Age int
 );

SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "student" table is already created, use the following SQL:

Syntax

SQL Server / MS Access:

 ALTER TABLE  student
 ALTER COLUMN Age int NOT NULL ;

Syntax

My SQL / Oracle (prior version 10G):

 ALTER TABLE  student
 MODIFY COLUMN Age int NOT NULL ;

Syntax

Oracle 10G and later:

 ALTER TABLE  student
 MODIFY Age int NOT NULL ;