Constraints and parameterized queries

Understand the mechanism

Application validation improves feedback; database constraints protect all write paths. NOT NULL, UNIQUE, CHECK and FOREIGN KEY address missing values, duplicates, business predicates and relationships respectively.

Enable and verify SQLite foreign_keys on each connection before beginning a transaction. Ordinary SQLite type affinity is not strict typing, so this schema additionally checks typeof(minutes).

Bind external values through the driver, for example Python connection.execute("SELECT name FROM courses WHERE id = ?", (course_id,)). Placeholders bind values, not table names or sort keywords; select dynamic identifiers from an explicit allowlist.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

PRAGMA foreign_keys=ON;
CREATE TABLE courses(id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE);
CREATE TABLE sessions(
 id INTEGER PRIMARY KEY,
 course_id INTEGER NOT NULL REFERENCES courses(id),
 minutes INTEGER NOT NULL CHECK(typeof(minutes)='integer' AND minutes BETWEEN 0 AND 240)
);
INSERT INTO courses VALUES(1,'Python');
INSERT INTO sessions VALUES(1,1,25);
SELECT course_id,minutes FROM sessions;

Trace and verify

The valid record is course 1 with 25 minutes. Independently try course_id=99, minutes=-1 and minutes=NULL to observe rejected writes.

Common mistakes and boundaries

A CHECK result of NULL is not a failed check, which is why NOT NULL is separate. The application must decide and execute rollback behavior after constraint errors.

Practice challenge

Try inserting a second course named Python and explain why the error is not a foreign-key failure.

Reference solution and reasoning

INSERT INTO courses VALUES(2,'Python'); violates UNIQUE on name. The new id is distinct; the duplicate name is the cause.

Further reading

• SQLite SQL reference