Table of Contents

Overview

In this tutorial, we will learn about SQLite NOT NULL constraint. We can specify this constraint to a column during the database table creation.

SQLite NOT NULL Constraint

The SQLite NOT NULL constraint ensures a column cannot have a NULL value. When you define a column with the NOT NULL constraint, it means that every row in the table must have a non-NULL value for that column.

Syntax

The general syntax of the constraint is as follows:

CREATE TABLE table_name (
column1 datatype NOT NULL,
column2 datatype,
…
);

By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a column to contain a value, meaning you cannot insert a new record or update a record without adding a value to this column.

Example

Let’s understand the concept with the help of an example. Consider the following CREATE TABLE statement.

CREATE TABLE demo_table (
column1 INTEGER NOT NULL,
column2 TEXT
);

The demo_table has two columns. column1 is NOT NULL, meaning the column cannot contain NULL values.

Insert data into the table using the following INSERT statement:

INSERT INTO demo_table VALUES(1,’ABC’);
INSERT INTO demo_table VALUES(2,’XYZ’);
INSERT INTO demo_table VALUES(3,’ ‘);
INSERT INTO demo_table VALUES(4);
INSERT INTO demo_table VALUES(4, NULL);
INSERT INTO demo_table VALUES(NULL,’PQR’);

SQLite NOT NULL Constraint

Attempting to set the column value to NULL when inserting a new row or updating an existing one causes a constraint violation.SQLite will generate a runtime error if you try to insert or update a row without providing a value for a column with the NOT NULL constraint.

In the following INSERT statement, an error has occurred.

INSERT INTO demo_table VALUES(NULL,’PQR’);

We tried inserting NULL into a column defined with NOT NULL constraint.
An error has occurred, and the insert was not successful. A violation of the NOT NULL constraint has occurred.

—