CHECK, UNIQUE, NOT NULL

This example shows all 3 constraint types.

CREATE TABLE grades (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    value INTEGER,
    CHECK (value IS NULL OR (value BETWEEN 0 AND 100))
);

INSERT INTO grades (name, value) VALUES ('Joe', 100);
INSERT INTO grades (name, value) VALUES ('Jane', 200);
INSERT INTO grades (name, value) VALUES ('Mary', NULL);
INSERT INTO grades (name) VALUES ('Peter');
INSERT INTO grades (name, value) VALUES ('Zorg', 'hello');

SELECT * FROM grades;

$sqlite3 < examples/range-constraint.sql

Error near line 9: CHECK constraint failed: value IS NULL OR (value BETWEEN 0 AND 100)
Error near line 12: CHECK constraint failed: value IS NULL OR (value BETWEEN 0 AND 100)
1|Joe|100
2|Mary|
3|Peter|