>_ IzzyLab

Hands-on task

This task is step 19 of the course “SQL with SQLite”

SQLite: CHECK constraint on a column

medium~7 min

Create a table products(id INTEGER PRIMARY KEY, name TEXT, price INTEGER) in the database /root/store.db with a CHECK constraint that forbids a non-positive price: price must be strictly greater than 0. The schema must accept a correct insert (price > 0) and reject a "bad" one (price 0 or negative).

What you'll practise:
  • check
  • constraints
  • create table
  • data validation
  • sqlite3

Topic breakdown

A CHECK constraint specifies a boolean condition that a value in a row must satisfy; if the condition is false, the insert or update is rejected with an error. Unlike foreign keys, CHECK always takes effect and doesn't require enabling any PRAGMA. It is declared in CREATE TABLE — right after the column or as a separate clause, with the condition written inside the parentheses using the column name. This way the database protects itself from obviously invalid data at the schema level.

What's checked

  1. a valid insert passes
  2. a negative price is rejected
  3. a zero price is rejected

What it looks like