SQLite Check Constraint

  Total Views : 23
  Comments : 0
 
  Keywords : SQLite add check constraint on table column with example, Use sqlite check constraint to add conditions to check before insert data with example, SQLite check constraint to validate data

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.

SQLite Check Constraint

In SQLite CHECK Constraint is used to define specific conditions on 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.

Syntax of SQLite Check 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 is greater than zero (0).

Example of SQLite Check Constraint

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 table and set CHECK constraint insert data in table using 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 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 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 error like as shown above.

 

Now, we also try to insert negative value in Price column using 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 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User