🎓 A1Learner
🔍

SQL Constraints

A constraint is a rule that a column's values must obey. The engine checks the rule on every insert and update.

Four rules on one table

The reviews table gives its columns their rules. Each rule guards against a different problem.

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  reviewer TEXT DEFAULT 'Anonymous',
  code TEXT UNIQUE
);

The default fills in

This insert leaves reviewer out. The SELECT shows the default in its place.

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  reviewer TEXT DEFAULT 'Anonymous',
  code TEXT UNIQUE
);
INSERT INTO reviews (review_id, product_id, rating, code) VALUES (1, 3, 5, 'R-100');
SELECT review_id, rating, reviewer, code FROM reviews;

A named reviewer passes

This insert names its reviewer. The stored row keeps the given name, not the default.

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  reviewer TEXT DEFAULT 'Anonymous',
  code TEXT UNIQUE
);
INSERT INTO reviews VALUES (2, 4, 4, 'Priya', 'R-101');
SELECT review_id, rating, reviewer, code FROM reviews;

Example

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  rating INTEGER CHECK (rating BETWEEN 1 AND 5),
  reviewer TEXT DEFAULT 'Anonymous',
  code TEXT UNIQUE
);
INSERT INTO reviews VALUES (1, 3, 9, 'Sam', 'R-200');

Key points

  • NOT NULL: a rule that refuses a missing value
  • UNIQUE: a rule that refuses a repeated value
  • DEFAULT: a value the engine fills in when an insert leaves the column out
  • CHECK: a rule that tests every value against a condition
  • One column can carry more than one rule.
  • A broken rule stops the whole statement.

In this example, the code builds the reviews table. The insert gives a rating of 9. The CHECK rule allows only ratings from 1 to 5, so SQLite refuses the row.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

An insert leaves out a column that has a DEFAULT rule. What happens?