Definition of NOT NULL in SQL
In SQL, data columns can contain NULL values. To change this default behavior, you can use a column constraint called NOT NULL:
CREATE TABLE People (
ID int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255) NOT NULL,
Year int
);
NOT NULL lets you make sure the data entries stored in a particular column are not NULL. Adding such a value to a column that has an SQL NOT NULL constraint applied would cause an error to fire.
Syntax for the NOT NULL Constraint
To add the SQL NOT NULL constraint, follow the syntax example below:
column_name data_type NOT NULL;
As you can see, you need to define the name of the column and the data type to apply the constraint. Let's review the code example we saw before once again and analyze it in depth this time:
CREATE TABLE People (
ID int NOT NULL,
LName varchar(255) NOT NULL,
FName varchar(255) NOT NULL,
Year int
);
As you can see, we applied the SQL NOT NULL constraint to three columns in the People table: ID, LName and FName. The first one holds integers, and the other two are both variable character fields which may contain up to 255 characters.
The fourth column is called Year and may contain integers as well as NULL values, as it has no NOT NULL constraint applied.
Why Use NOT NULL?
It is generally recommended to apply SQL NOT NULL to every column when creating tables for your data. It allows you to simplify queries: there's no need to use ISNULL(), IFNULL() and NULLIF() in the future, as you dont need to work with NULL values.
Usually, developers add the NOT NULL at the time of creating a column. However, you can also apply it to an existing column – just make sure it doesn't contain any NULL values beforehand.