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'