🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

Code has been added to clipboard!

SQL NOT NULL Constraint: What It Is and How to Use It

Reading time 2 min
Published Aug 9, 2017
Updated Oct 11, 2019

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:

Example
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.

DataCamp
Pros
  • 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
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid Certificates of completion
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free 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:

Example
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.