Constraints & Validation — SQL

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 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'
);

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

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.

📌 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

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.

What you will learn