SQLite CHECK Constraint

SQLite is a lightweight and embedded Relational Database Management System (commonly known as RDBMS). It is written in C Language. It supports standard SQL syntax. It is a server-less application which means it requires less configuration than any other client-server database (any database that accepts requests from a remote user is known as a client-server database). Its server-less aspect makes it a good fit for mobile, embedded, and simple desktop applications. It may not be a good fit for large-scale applications but it can offer various other aspects like simplicity and portability.

In this article, we are going to explore SQLite CHECK constraints. We going to explore its use cases with different scenarios.

CHECK Constraints

In SQLite, we have various functions and constraints. In this article, the main focus is on the ‘CHECK’ constraint. It is used to enforce a condition within a column or table. It allows only those values to be inserted or updated in the table that follows that specific condition of the CHECK constraint. In simple words, it enforces the values that are inserted or updated in the table/column.

Let’s take an example:

Suppose we have a table ‘voter’. In this table, we only want to insert or update those values whose ‘age’ is above 18 years. In this kind of scenario, we can take the help of the CHECK constraint.

Examples of CHECK Constraint

Let us see some of the examples of check constraints.

Example 1: CHECK Constraint with Single Condition

To implement the CHECK constraint, let’s create a table.

Table Name: ‘voter’

Command

CREATE TABLE voter(
  voter_id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  dob DATE,
  check(age >= 18)
 );

From the above command, it is clear that we have applied the condition on ‘age’ column. Now let’s insert some values to the table and display them.

Query:

---Data Insertion

INSERT INTO voter(voter_id,name,age,dob)
VALUES(101,'Vishu',19,'2003-02-24');
INSERT INTO voter(voter_id,name,age,dob)
VALUES(102,'Sumit',20,'2002-05-21');
INSERT INTO voter(voter_id,name,age,dob)
VALUES(103,'Neeraj',19,'2003-04-18');
INSERT INTO voter(voter_id,name,age,dob)
VALUES(104,'Aaysuh',21,'2001-06-15');

---Displaying the table data

SELECT * from voter;

Output:

Table – voter

Now, let’s try to enter some values that do not obey the current condition (age >=18 ).

Query:

INSERT INTO voter(voter_id,name,age,dob)
VALUES(110,'Karan',15,'2007-02-05');

Output:

Error-01

As we read the error, it says ” CHECK constraint failed age >= 18″. This means we have entered a value in the age column which is less than 18.

Example 2: CHECK Constraint with Multiple Condition

Let’s create the same table but this time we will add more than one condition inside our CHECK condition block.

Query:

CREATE TABLE voter(
  voter_id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  dob DATE,
  check(age >= 18 and voter_id in(101,102,103,104,105,106))
 );

From the above command, we have specified the ‘age’ column to accept only values above 18 and the ‘voter_id’ column to accept only those values which belong in List: [101,102,103,104,105,106].

We are using the table data from the Example 1.

Now let’s insert a value in which voter value is not present in the given condition list.

Query:

INSERT INTO voter(voter_id,name,age,dob)
VALUES(110,'Karan',21,'2000-02-05');

Output:

Error – 02

In the above image, we can clearly see the error message showing the violation of the conditions.

Let’s violate another condition, i.e. age condition.

Query:

INSERT INTO voter(voter_id,name,age,dob)
VALUES(105,'Karan',17,'2005-02-05');

Output:

Error – 03

Now let’s decode our condition.

Our condition: check(age >= 18 and voter_id in(101,102,103,104,105,106))

To successfully execute our command we need to fulfill both condition because we have used ‘and’ operator here. Instead of and if we had used ‘or’ operators then we only need one of the condition to be true in order to execute our command.

Let’s try to run a command that fulfills both conditions.

Query:

INSERT INTO voter(voter_id,name,age,dob)
VALUES(105,'Karan',21,'2000-02-05');

Output:

Table – voter 02

Therefore, after satisfying both conditions, our query was successfully executed.

Example 3: Adding CHECK Constraints to an Existing Table

Let’s create a table with no CHECK constraints.

Query:

CREATE TABLE voter(
  voter_id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  dob DATE
 );

Let’s keep the data the same as the previous two examples.

Now we need to add the same condition as in our Example 2.

Query:

BEGIN;
--- BEGIN is used to denote the beginning of an SQL command transaction

CREATE TABLE new_voter (
  voter_id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  dob DATE,
  CHECK (age >= 18 AND voter_id IN (101, 102, 103, 104, 105, 106))
);

INSERT INTO new_voter SELECT * FROM voter;

DROP TABLE voter;

ALTER TABLE new_voter RENAME TO voter;

COMMIT; 
---it is used to commit the changes.

To add the given condition in existing table ‘voter’, we need follow the below steps.

Step 1: Create a new table with same columns and with the conditions we desire to add.

Step 2: Copy the data of the previous table ‘voter’ into our new table ‘new_voter’.

Step 3: Drop the previous table ‘voter’.

Step 4: Replace the new table name with the old name.

Step 5: Commit all the changes we have done till now.

Conclusion

SQLite is a server-less application. Standard SQL syntax is supported. Both simple desktop apps and mobile applications work well with it. To insert or update a table or column in SQLite, the CHECK constraint enforces conditions in the values. The CHECK constraint makes it possible to limit the values that can be added or updated to a table that does not follows or obey to the specified criteria. Any modifications to the table’s data are restricted by CHECK if the condition evaluates to false.