What constraint types does SQLite support?

SQLite's constraint mechanism ensures data integrity and consistency:

1. NOT NULL Constraint

   • Ensures that the column does not accept NULL values
   • An error is reported if the constraint is violated during insert or update

   sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       name TEXT NOT NULL
   );

2. UNIQUE Constraint

   • Ensures that all values in the column are unique
   • Can be applied to a single column or a combination of multiple columns
   • Automatically creates an index to improve performance

   sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       email TEXT UNIQUE
   );

3. PRIMARY KEY Constraint

   • Uniquely identifies each row in the table
   • Automatically creates NOT NULL and UNIQUE constraints
   • Can be a single column or composite primary key

   sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       username TEXT
   );

4. FOREIGN KEY Constraint

   • Establishes relationships between tables
   • Ensures referential integrity
   • Need to enable foreign key constraints: PRAGMA foreign_keys = ON;

   sql
   CREATE TABLE orders (
       id INTEGER PRIMARY KEY,
       user_id INTEGER,
       FOREIGN KEY (user_id) REFERENCES users(id)
   );

5. CHECK Constraint

   • Defines conditions that column values must satisfy
   • Can include complex expressions

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

6. DEFAULT Constraint

   • Specifies a default value for a column
   • Uses the default value when no value is provided during insert

   sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       status TEXT DEFAULT 'active',
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );

7. Constraint Conflict Handling

   • Use ON CONFLICT clause to handle constraint conflicts
   • Optional values: ROLLBACK, ABORT, FAIL, IGNORE, REPLACE

   sql
   INSERT OR REPLACE INTO users (id, name) VALUES (1, 'John');

Proper use of constraints can ensure data quality and prevent insertion and updates of invalid data.