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

We have used Check contraint in integer columns to validate data while adding and updating. Here we will learn how to add constraints to Date columns.

Creating table with DATE column and CHECK constraint

Let us create one table with two columns to store date. We will add one constraint to one column to check input date is less than or equal to today's date.

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("CREATE TABLE IF NOT EXISTS my_dt(dt1 text, \
                      dt2 text,\
                      CONSTRAINT my_Check CHECK(dt1 <= date('now')))")
    my_conn.commit()
    print("my_dt  Table created successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

Let us INSERT data to this table

try:
    my_conn.execute("INSERT INTO my_dt(dt1,dt2) VALUES \
    ('2023-08-18','2020-12-13')")
    my_conn.commit()
    print("Row added  successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error)

Based on date setting of your system, the output will change if any future date is used then we will get the error message.

error:  CHECK constraint failed: my_Check

Adding new constraints

SQLite does not allow any changes in CONSTRAINT by using alter command. We have to create a new table by adding / updating CHECK constraints. Then copy the data from old table to new table and then delete the old table. Then change the name of the new table to old table.

Let us add one more constraint to ensure that column dt2 is always greate than or equal to the first column dt1. ( In a hotel billing system, Check-in date is less than or equal to Check-out date )

try:
    my_conn.execute("BEGIN")
    my_conn.execute("CREATE TABLE IF NOT EXISTS \
        my_dt_new(dt1 text, \
        dt2 text, \
        CONSTRAINT my_Check CHECK(dt1 <= date('now')),\
        CONSTRAINT my_check2 CHECK(date(dt2) >= date(dt1)))")
    my_conn.execute("INSERT INTO my_dt_new SELECT * FROM my_dt")
    my_conn.execute("DROP table my_dt")
    my_conn.execute("ALTER TABLE my_dt_new RENAME TO my_dt")
    my_conn.commit()
    print("Date constraint updated successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error) 
my_conn.commit()

Let us add some data to check the constraint. Before that we will understand date outputs using 'now'

q="SELECT date('now')"
r_set=my_conn.execute(q)
data_row=r_set.fetchone()
print(data_row[0])

Output is today's date ( based on your system date )

2020-08-19

By changing the query we can get different dates.

q="SELECT date('now','-1 day')" # yesterday date 
q="SELECT date('now','1 day')" # Tomorrow date

Using above code we will add date values to our table to check the constraints.

try:
    my_conn.execute("INSERT INTO my_dt(dt1,dt2) VALUES \
    (date('now','-1 day'),date('now','-2 day'))")
    my_conn.commit()
    print("Row added  successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error)

This will generate error ( value for column dt2 is less than dt1 )

error:  CHECK constraint failed: my_check2

View & Download ipynb file (.html format)

Sqlite insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite DATE Constraint CHECK

Creating table with DATE column and CHECK constraint

Adding new constraints

Subscribe