SQLite NOT NULL Constraint

SQLite is a very lightweight and embedded Relational Database Management System (RDBMS). It requires very minimal configuration and it is self-contained. It is serverless, therefore it is a perfect fit for mobile applications, simple desktop applications, and embedded systems. While it may not be a good fit for large-scale enterprise applications, it can offer simplicity, and portability and can be easy to use. In this article, we are going to cover its NOT NULL Constraint and its different aspects.

NOT NULL Constraint

SQLite possesses some useful constraints. NOT NULL Constraint is one of its constraints. This constraint ensures that a column or columns do not accept any NULL/Undefined values. In simple words, it makes the applied column accept only Not Null values. This helps in data integrity and prevents the insertion of incomplete information in the table.

Let’s take an Example Scenario:

Let us assume that we have created a table named ‘student’ and for its columns, we have name, roll, subject, and marks. Now we want the ‘name’ column to accept only values that are Not Null. In this kind of scenario, we can use NOT NULL Constraint.

Syntax:

CREATE TABLE table_name ( column_name datatype NOT NULL);

Examples of NOT NULL Constraint

Let us discuss some of the implementations of NOT NULL Constraint.

Example 1: Using NOT NULL Constraint in One Column

To perform any kind of operations, we need to create a table in our database. We can simply create a table with below command.

Table Name: geeksforgeeks

Command

CREATE TABLE geeksforgeeks (
    gfg_id INTEGER PRIMARY KEY,
    name TEXT ,
    courses INTEGER,
    score INTEGER NOT NULL,
    questions_solved INTEGER
);

In the above table, we have created score as our NOT NULL column. This means the score cannot accept any null values.

Let’s insert some values in our table,

---Insertion of Data

INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(01,'Vishu',10,100,55); 
INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(02,'Aayush',9,95,50); 
INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(03,'Neeraj',8,90,45); 
INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(04,'Sumit',7,85,40); 

---Displaying data
Select * from geeksforgeeks;

Output:

geeksforgeeks-table

As stated before, We have created score as our NOT NULL column. Let’s try to insert some null values in that column.

Query:

INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(05,'Harsh',6,NULL,25);

Output:

Error-01

In the above image, we can clearly read the error message, which reflects the null value that we have entered in our score column.

Example 2: Using NOT NULL Constraint in More Than One Column

In this example, we will use the same table with the same data as in Example 1. The only catch is this time we will make ‘name’ and ‘score’, both the columns with NOT NULL Constraint.

Query:

CREATE TABLE geeksforgeeks (
    gfg_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    courses INTEGER,
    score INTEGER NOT NULL,
    questions_solved INTEGER
);

Let’s try to insert some null values in both of these columns i.e. ‘name’ and ‘score’.

INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(05,NULL,6,NULL,25);

Output:

Error-2

This time we get an error message stating not null constraint failed. This means the name too will not accept any null values from now on.

Insert Null Values in the Columns with the NOT NULL

Can We Insert Null Values in the Columns Other Than Ones with the NOT NULL Constraint? Yes, we can insert null values in the columns other than the columns with not null constraint. Let us see how we can achieve this.

Let’s use the same table created in Example, Now we will insert null values in the other columns.

INSERT INTO geeksforgeeks(gfg_id,name,courses,score,questions_solved)
VALUES(05,'Harsh',NULL,35,25);

Output:

geeksforgeeks table with Null Values

As we can see courses is a simple columns with no constraints, therefore we can enter null values in them.

Conclusion

NOT NULL constraint in SQLite allows us to make a column in our table to accept only not null values. It helps to maintain data integrity and prevents the insertion of in complete data in our table. We can apply NOT NULL Constraint in one or more than one column in our table. This constraint enforces that the desired column to accept not null values however other columns can accept null values.