SQLite DATE Constraint CHECK

SQLite SQLite CHECK constraint

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 contraint. 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 selectOrder By


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer