SQLite Not Null Constraint

Here we will learn SQLite Not Null constraint with examples and how to use SQLite Not Null constraint to set condition on column not to allow NULL values with examples.

SQLite Not Null Constraint

In SQLite, Not Null Constraint is used to indicates that the column will not allow storing NULL values. 

 

Generally, in SQLite by default columns will allow NULL values in case if you have requirement like not to allow NULL values in column means then we need to add NOT NULL constraint on column.

 

Once we add NOT NULL Constraint on a required column and if we try to insert or update a NULL value in a new or existing row then it will throw an error because of constraint violation.

Syntax of SQLite Not Null Constraint

Following is the syntax of adding SQLite Not Null Constraint to the column.

 

CREATE TABLE tablename

(column1 INTEGER,

column2 TEXT NOT NULL,

column3 TEXT NOT NULL);

If you observe above SQLite Not Null Constraint syntax we are adding Not Null Constraint on multiple columns (column2, column3) and these columns will not allow null values.

Example of SQLite Not Null Constraint

Following is the example of SQLite Not Null constraint

 

CREATE TABLE BOOK

(Book_id INTEGER,

Book_name TEXT NOT NULL,

Publisher_name TEXT NOT NULL);

In above example we added Not Null Constraint on Book_name and Publisher_name columns and these columns will not allow null values.

 

Now run above query to create new table “Book” and insert some of records using following statements.

 

sqlite> INSERT INTO BOOK Values (1,'SQLITE','Shweta');

 

sqlite> SELECT * FROM BOOK;

 

book_id     Book_name   Publisher_name

----------  ----------  --------------

1           SQLITE      Shweta

If you observe above query we inserted all the values so No problem arises at the time of insertion.

 

Now run following SQLite statement to insert new row in Book table.

 

sqlite> INSERT INTO BOOK Values (2,'SQL Guide','');

 

sqlite> SELECT * FROM BOOK;

 

book_id     Book_name   Publisher_name

----------  ----------  --------------

1           SQLITE      Shweta

2           SQL Guide

Here also no problem arises at the time of insertion, though the value of second column is blank.

 

Now we will insert NULL value in Publisher_name column using following statement.

 

sqlite> INSERT INTO BOOK Values (1,'SQLITE',NULL);

 

Error: NOT NULL constraint failed: BOOK.Publisher_name

Here in above query we are forcefully inserting Publisher_name column value as NULL so it will violate the constraint and throw the error like as shown above statement.

 

This is how we can use SQLite Not Null constraint on columns to set restrictions like not to allow NULL values.