The staff sign-up form in your app already checks for duplicate emails. Then a teammate imports 500 employees with a script (a small program), straight into the database, without using the form. The next day there are two Budis with the same email, and one employee earning 0.
A constraint prevents that. A constraint is a rule you put on a table's column, and the database itself refuses any data that breaks it. It works like a clerk who checks every sign-up form. Fields marked required can't be empty, an email that's already registered is refused, a salary can't be zero, and a field left blank gets a standard value. In the database, the clerk is the database itself, each form is a new row, and the clerk's rules are the constraints. Your app may check too, but constraints are the last guard: data that breaks them never gets into the table, wherever it comes from.
A table with rules
This is the employees table from the exercise. The rules go after each column's data type:
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
salary REAL CHECK (salary > 0),
department TEXT DEFAULT 'General'
);
id INTEGER PRIMARY KEY AUTOINCREMENT: each row's unique number, filled in automatically as 1, 2, 3 and so on.name TEXT NOT NULL: NOT NULL means this column must be filled in. It may not beNULL(empty, no value yet).email TEXT UNIQUE NOT NULL: UNIQUE means no two rows may have the same email. BecauseNOT NULLis there too, the email is also required.salary REAL CHECK (salary > 0): CHECK is a rule you write yourself. The condition in the parentheses must be true for every row. Here the condition is: the salary is more than 0.department TEXT DEFAULT 'General': DEFAULT is a standard value. If anINSERTdoesn't mention this column, it becomes'General'automatically.
One column can have several rules at once, like email above.
What happens when a rule is broken
Budi is already in the table with the email [email protected]. Below are the next four INSERTs. Under each INSERT, a comment line shows the result. Each error message there is exactly what SQLite gives:
INSERT INTO employees (name, email, salary) VALUES ('Siti', '[email protected]', 9000);
-- goes in, department is 'General' automatically
INSERT INTO employees (name, email, salary) VALUES ('Budi Santoso', '[email protected]', 7000);
-- UNIQUE constraint failed: employees.email
INSERT INTO employees (email, salary) VALUES ('[email protected]', 5000);
-- NOT NULL constraint failed: employees.name
INSERT INTO employees (name, email, salary) VALUES ('Andi', '[email protected]', 0);
-- CHECK constraint failed: salary > 0
- For the three refused
INSERTs, the error message names the rule that was broken and its column. So read the message before you start guessing the cause. - A refused
INSERTis cancelled completely. No half-made row is left behind. - Siti's
INSERTdoesn't mentiondepartment, soDEFAULTfills it with'General'.
One thing that often surprises people: CHECK lets a NULL value through. An INSERT without salary still goes in, because the result of NULL > 0 isn't "false" but "unknown". CHECK only refuses a row when the result of its condition is "false". If a salary must be there, add NOT NULL too.
FOREIGN KEY: it must point at a row that exists
In the Table Relationships lesson, you met the foreign key: a column that holds another table's primary key. A FOREIGN KEY is also a constraint: the database makes sure the value in that column really exists in the other table. The attendance table records when employees came to work, and its employee_id column must point at an employee who exists in employees. There is no employee with id 99. Before you read the notes under the code, guess: in SQLite, is an attendance row for employee 99 refused, or does it just go in?
PRAGMA foreign_keys = ON;
CREATE TABLE attendance (
id INTEGER PRIMARY KEY,
employee_id INTEGER NOT NULL REFERENCES employees(id),
work_date TEXT NOT NULL
);
INSERT INTO attendance (employee_id, work_date) VALUES (99, '2026-10-08');
-- FOREIGN KEY constraint failed
The answer depends on the first line of that code, PRAGMA foreign_keys = ON;. With that line, the attendance row for employee 99 is refused. Without it, that row just goes in, with no error.
REFERENCES employees(id)means the value ofemployee_idmust exist in theidcolumn ofemployees.PRAGMA foreign_keys = ON;is a settings command that turns foreign key checking on. In SQLite this checking is off by default, so it must be turned on every time a connection (a link to the database) opens.- Deleting is checked too. While Budi still has rows in
attendance, aDELETEof Budi fromemployeesis refused with the same message. So attendance rows never point at an employee who is gone. If you want Budi's attendance rows deleted with him, writeREFERENCES employees(id) ON DELETE CASCADE.
📌 How MySQL and PostgreSQL differ
MySQL (with InnoDB, its default storage engine) and PostgreSQL always check FOREIGN KEYs, with no PRAGMA. MySQL has really checked CHECK only since version 8.0.16. Older versions accept a CHECK in the table, and then silently ignore it. The length limit in VARCHAR(255) (text of at most 255 characters) is also treated differently: MySQL and PostgreSQL refuse longer text, while SQLite stores it anyway. NOT NULL, UNIQUE and DEFAULT work the same in all three databases.
⚠️ Rules never fitted, or fitted to the wrong column
- Thinking the app's checks are enough. Data can come in many ways: another form, an import script, or a SQL command typed by hand. Constraints guard all of them.
UNIQUEon the wrong column. The exercise checks that the table has exactly oneUNIQUErule, onemail. People's names can repeat.- Forgetting
NOT NULLon one column. In the exercise,nameandemailare both required, and both are checked. - Leaving out the
CHECK. WithoutCHECK (salary > 0), a salary of 0 or less still goes in, with no error. - Forgetting
PRAGMA foreign_keys = ONin SQLite. The foreign key is written in the table, but SQLite doesn't check it.
TL;DR: A constraint is a rule on a table's column, and the database itself refuses data that breaks it. NOT NULL means the column must be filled in, and UNIQUE means the value can't repeat. CHECK (condition) means the condition in the parentheses must be true. DEFAULT value fills a column the INSERT doesn't mention, and FOREIGN KEY ... REFERENCES makes sure the value exists in another table. Breaking a rule cancels the whole INSERT, with an error message naming the rule that was broken (for NOT NULL, UNIQUE and CHECK, the column too). CHECK lets NULL (empty, no value yet) through, and in SQLite foreign keys are only checked after the command PRAGMA foreign_keys = ON.
⚡ Rules on the columns
Write:
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
salary REAL CHECK (salary > 0),
department TEXT DEFAULT 'General'
);
Meaning: The rules written after each column's type are called constraints, and the database refuses data that breaks them. NOT NULL means it must be filled in. UNIQUE means its value can't repeat in two rows. CHECK means the condition in its parentheses must be true. DEFAULT fills a column the INSERT doesn't mention.
The exercise checks that the table has exactly one UNIQUE, on email.
⚡ Breaking a rule gets refused
Write:
INSERT INTO employees (name, email, salary) VALUES ('Andi', '[email protected]', 0);
Meaning: Refused with CHECK constraint failed: salary > 0, because a salary of 0 doesn't meet the condition salary > 0. The error names the rule and the column, and a refused INSERT is cancelled completely.
⚡ CHECK and NULL
Guess first: there's a CHECK (salary > 0), and you INSERT without a salary. Does it go in?
It goes in. Without salary, the value is NULL (empty). NULL > 0 isn't false, it's "unknown", so CHECK lets it through. If salary must be filled in, add NOT NULL.
Meaning: CHECK only refuses a row when the result of its condition is clearly false.
⚡ FOREIGN KEY in SQLite
Write:
PRAGMA foreign_keys = ON;
Meaning: A foreign key is a column that points at a row in another table. For example, the employee_id column in the attendance table is written with REFERENCES employees(id): its value must exist in the id column of the employees table. In SQLite this check is off by default, so turn it on first with the command above.