SQLite | Table | NOT NULL Constraint

Tags:

Set a NOT NULL constraint on a column when it must not store NULL. This article explains how to use the NOT NULL constraint.

What Is a NOT NULL Constraint?

A column with a NOT NULL constraint cannot store NULL. Use the following syntax to set a NOT NULL constraint on a column.

CREATE TABLE table_name (column_name NOT NULL, ...);

Let’s set the constraint. The following statement creates a table with a NOT NULL constraint on the name column.

create table user (name text not null, address text);

sqlite> create table user (name text not null, address text);
sqlite>

Now add some data. The address column can store NULL.

insert into user values ('dekuma', 'Seoul');
insert into user values ('kimkc', null);

sqlite> insert into user values ('dekuma', 'Seoul');
sqlite> insert into user values ('kimkc', null);
sqlite>

Because the name column has a NOT NULL constraint, attempting to store NULL displays the error Error: NOT NULL constraint failed: user.name.

insert into user values (null, 'Busan');

sqlite> insert into user values (null, 'Busan');
Error: NOT NULL constraint failed: user.name
sqlite>

Omitting the value of a column with a NOT NULL constraint causes the same error because SQLite would otherwise store NULL automatically.

insert into user (address) values ('Daejeon');

sqlite> insert into user (address) values ('Daejeon');
Error: NOT NULL constraint failed: user.name
sqlite>

Use a NOT NULL constraint for columns that must always have a value when data is inserted.