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.
- Easy to use with a learn-by-doing approach
- Offers quality content
- Gamified in-browser coding experience
- The price matches the quality
- Suitable for learners ranging from beginner to advanced
- Free certificates of completion
- Focused on data science skills
- Flexible learning timetable
- Simplistic design (no unnecessary information)
- High-quality courses (even the free ones)
- Variety of features
- Nanodegree programs
- Suitable for enterprises
- Paid Certificates of completion
- A wide range of learning programs
- University-level courses
- Easy to navigate
- Verified certificates
- Free learning track available
- University-level courses
- Suitable for enterprises
- Verified certificates of completion
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.