Python SQLite constraint check to validate data while adding and updating data

Check constraint ensures that data is validated before adding to table. It ensures the accuracy and reliability of the data.

Creating table with CHECK constraint

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER CHECK(age>=5), \
email TEXT UNIQUE,\
class_id INTEGER)'

SQLite Database Constraints for Column Level Data validation: Part 1 #sqlite

At column level we can assign constraint.

CREATE TABLE <table_name>(
column_name data_type check(expression),
column_name data_type)

While adding data the validation is done. Check this example.

query='''INSERT INTO `students` \
  (`student_id`, `name`, `age`, `email`,`class_id`) VALUES \
  (6,'abc',4,'abc6@example.com',9)'''

The output is here

error:  CHECK constraint failed: age>=5

We can increase the age value to 8, this is allowed.

query='''INSERT INTO `students` \
  (`student_id`, `name`, `age`, `email`,`class_id`) VALUES \
  (6,'abc',8,'abc6@example.com',9)'''

We will change the age column to include a range of allowed integer.

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER CHECK(age>=5 and age <=20), \
email TEXT UNIQUE,\
class_id INTEGER)'

This query will generate error

query='UPDATE students SET age = 22 WHERE student_id=2'
try:
  r_set=my_conn.execute(query)
except sqlite3.Error as my_error:
    print("error: ",my_error)
else:
  print('No. of records added : ',r_set.rowcount)

error:  CHECK constraint failed: age>=5 and age <=20

Example : Data must be from available list

Let us restrict our class_id of student table to 1, 2 , 3 only. So any value other than these three values are not accepted in class_id column.

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER,  \
email TEXT UNIQUE,\
class_id INTEGER,\
CONSTRAINT my_check1 CHECK(age>=5 and age <=20),\
CONSTRAINT my_check2 CHECK(class_id in (1,2,3)))'

Let us try to add record where value of class column is not within the accepted list

query='''INSERT INTO `students` \
  (`student_id`, `name`, `age`, `email`,`class_id`) VALUES \
  (7,'abc',8,'abc7@example.com',4)'''
try:
  r_set=my_conn.execute(query)
except sqlite3.Error as my_error:
    print("error: ",my_error)
else:
  print('No. of records added : ',r_set.rowcount)

Output

error:  CHECK constraint failed: my_check2

Copy this code from Github and run in your Google colab platform to Create SQLite database students table with all conststraints
https://github.com/plus2net/Python-basics/blob/main/sqlite_constraints.ipynb

View & Download sqlite-check-constraint ipynb file (.html format)

Accept only Integer data

SQLite Constraints allowing Integers, range of data and options from a list only: Part 2 #sqlite

Here we have declared class_id column as Integer, however it will accept the data 'a' ( which is a string ).

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER,  \
email TEXT UNIQUE,\
class_id INTEGER )'

query='''INSERT INTO `students` \
  (`student_id`, `name`, `age`, `email`,`class_id`) VALUES \
  (7,'abc',8,'abc7@example.com','a')'''
try:
  r_set=my_conn.execute(query)
except sqlite3.Error as my_error:
    print("error: ",my_error)
else:
  print('No. of records added : ',r_set.rowcount)

Output

No. of records added :  1

String data 'a' is allowed in a Integer coloumn in SQLite database. As per the SQLite manual it is not a bug.

This is a feature, not a bug. SQLite uses dynamic typing. It does not enforce data type constraints. Data of any type can (usually) be inserted into any column. You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns.
SQLite FAQ

To enforce the data restriction we will add check(). Here in class_id column we are accepting only integer.

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER,  \
email TEXT UNIQUE,\
class_id INTEGER check(typeof(class_id)="integer") )'

query='''INSERT INTO `students` \
  (`student_id`, `name`, `age`, `email`,`class_id`) VALUES \
  (7,'abc',8,'abc7@example.com','a')'''
try:
  r_set=my_conn.execute(query)
except sqlite3.Error as my_error:
    print("error: ",my_error)
else:
  print('No. of records added : ',r_set.rowcount)

error:  CHECK constraint failed: typeof(class_id)="integer"

Check constrains in Date field.

We will lean how to add CHECK constraints to Date fields. There are many application using date constrains. Here are some frequently used constraints.

In a hotel billing system Check-in date can’t be more greater than Check-out date. Date of admission can’t be more than current date.

Summary

query='CREATE TABLE students (\
student_id INTEGER PRIMARY KEY,\
name TEXT NOT NULL,\
age INTEGER,  \
email TEXT UNIQUE,\
class_id INTEGER check(typeof(class_id)="integer"),\
CONSTRAINT my_check1 CHECK(age>=5 and age <=20),\
CONSTRAINT my_check2 CHECK(class_id in (1,2,3)))'

Constraints in SQLite help maintain data integrity and consistency. Using constraints like PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT, and FOREIGN KEY ensures the accuracy and reliability of the data in your database.

Add Constraint Check to Validate Date
Sqlite insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite Constraint CHECK

Creating table with CHECK constraint

Example : Data must be from available list

Accept only Integer data

Check constrains in Date field.

Summary

Subscribe