SQLite NOT NULL constraint

An SQLite NOT NULL constraint is assigned to a column definition. It is used most often when defining a column when creating a table in the database. NOT NULL is not a constraint at the table level but at the column level.

Syntax

Here is the syntax of SQLite NOT NULL constraint:

CREATE TABLE table_name(
	column_name1 datatype NOT NULL,
	column_name2 datatype NOT NULL,
	column_name3 datatype NOT NULL,
	...
);

Example

In the following example we will create a table with columns set with SQLite NOT NULL constraint and columns without constraint to show how the NOT NULL constraint works.

The NOT NULL constraint can be set to a column defined as a primary key, but also to columns without other existing constraints.

CREATE TABLE books
(
ID 		INTEGER PRIMARY KEY NOT NULL,
NAME 		VARCHAR(250) NOT NULL,
PRICE 		INTEGER,
DESCRIPTION 	VARCHAR(1000),
);

INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (1, 'SQLite', 10, 'Learn SQLite');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (2, 'SQL', 20, 'SQL for beginners');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (3, 'PL/SQL', 30, 'PL/SQL book');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (4, 'PHP', NULL, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (5, 'Python', 20, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (6, 'HTML', 40, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (7, 'CSS', NULL, NULL);

SELECT * FROM books;

Table of books

ID	NAME	PRICE	DESCRIPTION
1	SQLite	10	Learn SQLite
2	SQL	20	SQL for beginners
3	PL/SQL	30	PL/SQL book
4	PHP	NULL	NULL
5	Python	20	NULL
6	HTML	40	NULL
7	CSS	NULL	NULL

Let’s try to insert in the books table an empty row that only contains the value in the id column.

INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (8, NULL, NULL, NULL);

The SQLite database will return the following error:

NOT NULL constraint failed: books.NAME