Constraints: NOT NULL, UNIQUE, PK, FK, CHECK, DEFAULT

Constraints enforce data quality at the database level — not only in app code. They prevent bad rows from being inserted.

Constraints — rules on data

Constraints enforce data quality at the database level — not only in app code. They prevent bad rows from being inserted.

Real-life example: Constraints are school rules printed on the handbook — no student without a name, no duplicate roll numbers.

NOT NULL — column must have a value
UNIQUE — no duplicate values in the column
PRIMARY KEY — unique row identifier
FOREIGN KEY — links to another table
CHECK — custom validation rule
DEFAULT — value when none is provided

NOT NULL — required values

NOT NULL rejects INSERT or UPDATE that leaves the column empty.

Real-life example: NOT NULL on full_name means every student row must have a name — blank rows are not allowed.

Required student name

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  city VARCHAR(80)
);

UNIQUE — no duplicates

UNIQUE ensures every value in the column (or column group) appears only once — like a unique email per student.

Real-life example: UNIQUE on email is one mailbox per student — two people cannot share the same address.

Unique email

ALTER TABLE students ADD email VARCHAR(120) UNIQUE;

PRIMARY KEY — main row identifier

PRIMARY KEY uniquely identifies each row. One per table. It implies NOT NULL and UNIQUE.

Real-life example: PRIMARY KEY is the roll number — one number, one student, forever in that table.

Composite primary key on enrollments

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  PRIMARY KEY (student_id, course_id)
);

FOREIGN KEY — link tables safely

FOREIGN KEY points to a PRIMARY KEY in another table. It stops orphan rows — enrollments for students who do not exist.

Real-life example: FOREIGN KEY is 'this enrollment must belong to a real student_id in the students table'.

Enrollment references student and course

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5, 2),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CHECK — custom rules

CHECK validates expressions — score between 0 and 100, grade_level positive, etc.

Real-life example: CHECK is the rule 'score cannot be above 100' enforced automatically on every insert.

Valid score range

ALTER TABLE enrollments
ADD CONSTRAINT chk_score CHECK (score >= 0 AND score <= 100);

DEFAULT — automatic value

DEFAULT fills a column when INSERT omits it — enrollment_date defaults to today, status defaults to 'active'.

Real-life example: DEFAULT is the pre-printed 'Date: ___' on a form that auto-stamps today's date if left blank.

Default enrollment status

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  status VARCHAR(20) DEFAULT 'active',
  enrolled_at DATE DEFAULT CURRENT_DATE
);