CHECK constraint failed

This error occurs when an INSERT or UPDATE violates a CHECK constraint. CHECK constraints validate that values meet specific conditions.

The CHECK constraint failed error means your data doesn't satisfy a validation rule defined on the table.

Understanding the Error

SQLITE_CONSTRAINT: CHECK constraint failed: users

A CHECK constraint defines rules that data must follow.

Common Causes

1. Value Out of Range

SQL

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  price REAL CHECK(price > 0),
  quantity INTEGER CHECK(quantity >= 0)
);

-- Fails: negative price
INSERT INTO products (price, quantity) VALUES (-10, 5);

-- Fails: negative quantity
INSERT INTO products (price, quantity) VALUES (10, -5);

2. Invalid Status Value

SQL

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT CHECK(status IN ('pending', 'shipped', 'delivered'))
);

-- Fails: invalid status
INSERT INTO orders (status) VALUES ('unknown');

3. Length Constraints

SQL

CREATE TABLE users (
  username TEXT CHECK(length(username) >= 3 AND length(username) <= 20)
);

-- Fails: too short
INSERT INTO users (username) VALUES ('ab');

4. Conditional Constraints

SQL

CREATE TABLE events (
  start_date TEXT,
  end_date TEXT,
  CHECK(end_date >= start_date)
);

-- Fails: end before start
INSERT INTO events VALUES ('2024-12-31', '2024-01-01');

How to Fix It

Solution 1: Provide Valid Data

SQL

-- Check the constraint definition
SELECT sql FROM sqlite_master WHERE name = 'products';

-- Insert valid data
INSERT INTO products (price, quantity) VALUES (10.99, 5);

Solution 2: Validate Before Insert

JAVASCRIPT

function insertProduct(db, price, quantity) {
  if (price <= 0) {
    throw new Error('Price must be positive');
  }
  if (quantity < 0) {
    throw new Error('Quantity cannot be negative');
  }

  db.run(
    'INSERT INTO products (price, quantity) VALUES (?, ?)',
    [price, quantity]
  );
}

Solution 3: Remove the Constraint

If the constraint is wrong, recreate the table:

SQL

-- Create new table without constraint
CREATE TABLE products_new (
  id INTEGER PRIMARY KEY,
  price REAL,
  quantity INTEGER
);

-- Copy data
INSERT INTO products_new SELECT * FROM products;

-- Swap tables
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;

Solution 4: Modify the Constraint

SQL

-- Add more permissive constraint
CREATE TABLE products_new (
  id INTEGER PRIMARY KEY,
  price REAL CHECK(price >= 0),  -- Allow zero
  quantity INTEGER CHECK(quantity >= -10)  -- Allow some negative
);

Viewing CHECK Constraints

SQL

-- See table definition including constraints
SELECT sql FROM sqlite_master WHERE type='table' AND name='products';

-- Output:
-- CREATE TABLE products (
--   id INTEGER PRIMARY KEY,
--   price REAL CHECK(price > 0),
--   quantity INTEGER CHECK(quantity >= 0)
-- )

Best Practices

Validate in application before database
Use clear error messages in app for constraint violations
Document constraints for team awareness
Test edge cases (zero, negative, empty, null)
Keep constraints simple - complex logic in application