The relational model and constraints
Define tables with keys and constraints so the database rejects invalid data.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.execute("CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")
db.execute(
"CREATE TABLE employee ("
"id INTEGER PRIMARY KEY, name TEXT NOT NULL, "
"salary INTEGER NOT NULL CHECK (salary > 0), "
"department_id INTEGER NOT NULL REFERENCES department(id))"
)
db.execute("INSERT INTO department (id, name) VALUES (1, 'Engineering')")
db.execute("INSERT INTO employee (name, salary, department_id) VALUES ('Ada', 120, 1)")
bad_statements = [
"INSERT INTO employee (name, salary, department_id) VALUES ('Bob', 100, 99)",
"INSERT INTO employee (name, salary, department_id) VALUES ('Cy', -5, 1)",
"INSERT INTO department (id, name) VALUES (2, 'Engineering')",
]
for statement in bad_statements:
try:
db.execute(statement)
except sqlite3.IntegrityError as error:
print(str(error).split(":")[0])
print(db.execute("SELECT COUNT(*) FROM employee").fetchone()[0])FOREIGN KEY constraint failed CHECK constraint failed UNIQUE constraint failed 1
Explanation
A relational database stores data in relations, which SQL calls tables. A table has named columns with types, and each row is one record. The model was introduced by E. F. Codd in 1970 and is the basis of systems such as PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.
Keys and constraints let the database itself protect the data. A PRIMARY KEY uniquely identifies each row. UNIQUE forbids duplicates in a column, NOT NULL forbids missing values, CHECK enforces a condition on each row, and a FOREIGN KEY requires that a value in one table match an existing row in another. This is referential integrity: an employee cannot belong to a department that does not exist.
The program creates two tables and then tries three invalid inserts. Each fails with sqlite3.IntegrityError, and only the one valid employee row remains. SQLite checks foreign keys only after you turn them on with PRAGMA foreign_keys = ON, and that setting applies per connection. Server databases such as PostgreSQL enforce them by default.
Explore the dry run
import sqlite3 db = sqlite3.connect(":memory:")db.execute("PRAGMA foreign_keys = ON")db.execute("CREATE TABLE department (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE)")db.execute( "CREATE TABLE employee (" "id INTEGER PRIMARY KEY, name TEXT NOT NULL, " "salary INTEGER NOT NULL CHECK (salary > 0), " "department_id INTEGER NOT NULL REFERENCES department(id))")db.execute("INSERT INTO department (id, name) VALUES (1, 'Engineering')")db.execute("INSERT INTO employee (name, salary, department_id) VALUES ('Ada', 120, 1)") bad_statements = [ "INSERT INTO employee (name, salary, department_id) VALUES ('Bob', 100, 99)", "INSERT INTO employee (name, salary, department_id) VALUES ('Cy', -5, 1)", "INSERT INTO department (id, name) VALUES (2, 'Engineering')",]for statement in bad_statements: try: db.execute(statement) except sqlite3.IntegrityError as error: print(str(error).split(":")[0])print(db.execute("SELECT COUNT(*) FROM employee").fetchone()[0])Follow the example
Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.
View the complete dry-run table
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| SQLite enforces foreign keys only when this per-connection setting is on. | — | — | |
| The parent row exists first, so employees can refer to department 1. | department: (1, Engineering) | — | |
| Each invalid statement is rejected: department 99 does not exist, the salary violates CHECK, and the name is a duplicate. | 3 statements rejected | — | |
| Only the valid employee row was stored. | employee rows = 1 | FOREIGN KEY constraint failed CHECK constraint failed UNIQUE constraint failed 1 |
Cost and common mistakes
Each insert checks its constraints through indexes, typically O(log n) time per constraint check for a table of n rows.
- In SQLite, foreign keys are not enforced unless PRAGMA foreign_keys = ON is executed on every new connection.
- A constraint in the database protects every program that uses it. Checking only in application code leaves other clients, scripts, and future code unprotected.
- NULL is not a value: it means unknown or missing, and comparisons with NULL are neither true nor false. Use IS NULL, not = NULL.
Try it yourself
Add a UNIQUE constraint on employee.name and predict what happens when 'Ada' is inserted again.
Reveal answer and reasoning
The second insert raises sqlite3.IntegrityError with a message starting UNIQUE constraint failed, and the row is not stored.