Cambridge Syllabus Notes

📚 Databases: Applying Validation to Fields

What is Validation?

Think of a database as a giant library. Validation is the librarian’s rulebook that ensures every book (record) is stored correctly: the title isn’t empty, the author’s name isn’t too long, and the ISBN is a valid number. In SQL, we use constraints to enforce these rules automatically.

Field Types & Common Constraints

VARCHAR(n) – Text up to n characters. Use NOT NULL to require a value.
INT – Whole numbers. Combine with CHECK (value >= 0) to avoid negative ages.
DATE – Dates. Use CHECK (date <= CURRENT_DATE) to prevent future dates.
DECIMAL(p,s) – Numbers with p total digits and s after the decimal. Great for prices.
ENUM – A list of allowed values. Perfect for status fields like 'active', 'inactive'.

Common Validation Rules

NOT NULL – The field must have a value.
UNIQUE – No two rows can share the same value.
PRIMARY KEY – Combines NOT NULL + UNIQUE; uniquely identifies a row.
CHECK – Custom condition, e.g. CHECK (age >= 0).
FOREIGN KEY – Links to another table, ensuring referential integrity.

Example: Student Table

We want a table that stores student data with the following rules:

Student ID is a unique 6‑digit number.
First and last names are required and max 30 characters.
Age must be between 5 and 25.
Enrollment date cannot be in the future.

CREATE TABLE Students (
  student_id INT NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  age INT CHECK (age BETWEEN 5 AND 25),
  enrollment_date DATE CHECK (enrollment_date <= CURRENT_DATE),
  PRIMARY KEY (student_id),
  UNIQUE (student_id)
);

Using ENUM for Status

Suppose we want a status field that can only be 'active', 'graduated', or 'withdrawn':

ALTER TABLE Students
ADD status ENUM('active', 'graduated', 'withdrawn') NOT NULL DEFAULT 'active';

Foreign Key Example

Linking students to their class:

CREATE TABLE Classes (
  class_id INT PRIMARY KEY,
  class_name VARCHAR(50) NOT NULL
);

ALTER TABLE Students
ADD class_id INT,
ADD CONSTRAINT fk_class
  FOREIGN KEY (class_id)
  REFERENCES Classes(class_id);

Common Mistakes to Avoid

Using NULL for required fields.
Forgetting UNIQUE on fields that should be distinct.
Not setting CHECK constraints for numeric ranges.
Allowing future dates in DATE fields.

Exam Tips Box

🛠️ Tip 1: Remember the order of constraints.

When writing a CREATE TABLE statement, list NOT NULL first, then UNIQUE, then CHECK, and finally PRIMARY KEY for clarity.

⚠️ Tip 2: Use CHECK for ranges.

For example, CHECK (age BETWEEN 5 AND 25) is clearer than multiple CHECK statements.

📌 Tip 3: Test with sample data.

Insert a few rows and try violating a constraint to see the error message. This helps you remember what each rule does.

Practice Exercise

Create a table Books with fields: isbn (unique 13‑digit), title (max 100 chars, not null), author (max 50 chars), price (decimal 10,2, must be ≥ 0), and published_year (int, between 1900 and current year).
Write the SQL statement including all appropriate constraints.
Insert a row that violates the price constraint and note the error.

Apply validation to fields in a database