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

Check constraint ensures that data is validated before adding to table.

Creating table with CHECK constraint

   CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                   name text, 
                   class text, 
                   mark integer, 
                   sex text,
                   CONSTRAINT my_Check CHECK(mark >= 10)
                    )

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.

INSERT INTO student (id,name,class,mark,sex) VALUES 
    (1,'John Deo','Four',9,'female')

The output is here

error:  CHECK constraint failed: my_Check

We can increase the mark value to 100, this is allowed.

INSERT INTO student (id,name,class,mark,sex) VALUES 
    (1,'John Deo','Four',100,'female')

This will not allow us to update the mark value to below 10. This will generate error as the mark will fall below 10.

UPDATE student SET mark=mark-91

However we can deduct mark upto 90. This query will work as final mark became 10.

UPDATE student SET mark=mark-90

Changing the CHECK constraint

SQLite does not allow change in CHECK constraints by using ALTER command. We have to create another table with new constraints, copy the data to new table and then drop the old (previous) table.

my_conn is the connection string to SQLite database. Check the .ipynb file at the end of this page to get the detail about connection and creating tables.

try:
    my_conn.execute("BEGIN")
    my_conn.execute("CREATE TABLE IF NOT EXISTS \
        student_new(id integer primary key, \
        name text, \
        class text, \
        mark integer, \
        sex text,\
        CONSTRAINT my_Check CHECK(mark >= 10 AND mark <=100))")
    my_conn.execute("INSERT INTO student_new SELECT * FROM student")
    my_conn.execute("DROP table student")
    my_conn.execute("ALTER TABLE student_new RENAME TO student")
    my_conn.commit()
    print("Student constraint updated successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

Now we have student table with new CHECK constraint. We will try to increase the mark beyond 100 by using UPDATE query.

try:
    r_set=my_conn.execute("UPDATE student SET mark=101")
    print("Records Updated : ",r_set.rowcount)
    my_conn.commit()
    #print("Student updated   successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

Output is here

error:  CHECK constraint failed: my_Check

Example : Data must be from available list

Let us restrict our classes of student table to One, Two , Three only. So any value other than these three values are not accepted in Class column.

Note that while copying old data to new table our constraints may not allow any other classes ( if old table already have not allowed data ), so better empty the student table and then add the constraint check to it.

Here is the code to add new constrain to our student table class column to accept only limited values.

try:
    my_conn.execute("BEGIN")
    my_conn.execute("CREATE TABLE IF NOT EXISTS \
        student_new(id integer primary key, \
        name text, \
        class text, \
        mark integer, \
        sex text,\
        CONSTRAINT my_Check CHECK(mark >= 10 AND mark <=100),\
        CONSTRAINT my_check2 CHECK(class in('One','Two','Three')))")
    my_conn.execute("INSERT INTO student_new SELECT * FROM student")
    my_conn.execute("DROP table student")
    my_conn.execute("ALTER TABLE student_new RENAME TO student")
    my_conn.commit()
    print("Student constraint updated successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

If you are getting any error message saying

error:  CHECK constraint failed: my_check2

Delete all recods of student table and then try as our new constraint is not allowing records of old table having different data for class column.

Use this code to delete all records of student table and then try to add new constraint.

try:
    my_conn.execute(" DELETE FROM student ")
    my_conn.commit()
    print("All records of Student Table deleted")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

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

my_data=('Deo Greek','Six',35,'male') # tuple to pass values
q="INSERT INTO student (name,class,mark,sex) values (?,?,?,?)"
try:
    my_cursor=my_conn.execute(q,my_data)
    my_conn.commit()
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

error:  CHECK constraint failed: my_check2

We used Parameter query to add records in above code.
View & Download sqlite-check-constraint ipynb file (.html format)

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.
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

Changing the CHECK constraint

Example : Data must be from available list

Check constrains in Date field.

Subscribe