Here we will learn SQLite Check Constraint with example and how to use SQLite Check constraint to validate data before inserting into table columns with examples.
In SQLite, CHECK Constraint is used to define specific conditions on a column to validate those conditions while inserting or updating the data in table columns.
Generally, in SQLite the CHECK Constraint is added to a column while creating table using CREATE Statement or altering / modifying table column using ALTER Statement.
Once we set CHECK Constraint on column while inserting or updating data it will validate whether the given data satisfying defined conditions or not in case if it return False then no new row will be inserted or updated due to violation of the constraint.
Following is the syntax of adding check constraint on table column.
CREATE TABLE tablename
(col1 INTEGER PRIMARY KEY,
col2 TEXT NOT NULL,
col3 INTEGER CHECK (col3 > 0));
If you observe above syntax we added Check Constraint for column “col3” to allow only values which are greater than zero (0).
We will see how to use SQLite CHECK Constraint with examples. Following is the example of using SQLite CHECK Constraint on table column while creating table.
CREATE TABLE BOOK
(Book_id INTEGER PRIMARY KEY,
Book_name TEXT NOT NULL,
Pub_name TEXT NOT NULL,
PRICE INTEGER CHECK (PRICE > 0));
Here in the above example we added CHECK constraint to the PRICE column to check whether the inserting or updating values greater than zero or not.
Once we create a table and set CHECK constraint to insert data in the table using the following statement.
INSERT INTO BOOK Values(1,'Gita','JBL',150);
sqlite> SELECT * FROM BOOK;
Book_id Book_name Pub_name PRICE
---------- ---------- ---------- ----------
1 Gita JBL 150
When we execute the above query there will not be any problem because we are inserted number which is greater than zero in Price Column.
Now, we will try to insert Price as a zero (0) value using the following statement.
sqlite> INSERT INTO BOOK VALUES(2,'RAMAYAN','THL',0);
Error: CHECK constraint failed: BOOK
When we try to insert 0 in Price column, it will violate CHECK constraint and throw an error like as shown above.
Now, we also try to insert negative value in the Price column using the following statement.
sqlite> INSERT INTO BOOK VALUES(2,'RAMAYAN','THL',-300);
Error: CHECK constraint failed: BOOK
When we try to insert negative value in Price column again it will violate the CHECK constraint and throws an error.
This is how we can use SQLite Check Constraint to set the conditions on column to check while inserting or updating data based on our requirements.