Skip to main content

Understanding Data Types and Constraints

Choosing the right data types and constraints keeps the University database trustworthy. In this lesson we examine the columns that power our schema and discuss why each type and rule exists.

Learning Goals

  • Map real-world university concepts to SQL data types
  • Understand how PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK protect data
  • Practice applying constraints when extending the schema
  • Spot common pitfalls when selecting types

Review: University Table Definitions​

department
CREATE TABLE department (
  dept_id   INTEGER PRIMARY KEY,
  dept_name TEXT NOT NULL UNIQUE
);
  • INTEGER PRIMARY KEY produces unique, index-friendly identifiers.
  • TEXT NOT NULL UNIQUE ensures distinct department names.
student
CREATE TABLE student (
  student_id      INTEGER PRIMARY KEY,
  first_name      TEXT    NOT NULL,
  last_name       TEXT    NOT NULL,
  email           TEXT    UNIQUE,
  dept_id         INTEGER,
  enrollment_year INTEGER CHECK (enrollment_year >= 2000),
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
  • Names use TEXT; we allow NULL emails for students who have not activated accounts.
  • dept_id can be NULL for undeclared majors, but when present it must match an existing department.
  • CHECK guards against accidentally entering impossible enrollment years.
course
CREATE TABLE course (
  course_id     INTEGER PRIMARY KEY,
  course_code   TEXT    NOT NULL UNIQUE,
  course_title  TEXT    NOT NULL,
  credits       INTEGER NOT NULL CHECK (credits > 0 AND credits <= 6),
  dept_id       INTEGER NOT NULL,
  instructor_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id),
  FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id)
);
  • credits uses INTEGER with a CHECK constraint to limit values to a realistic range.
  • course_code is UNIQUE, preventing duplicate catalog numbers.
enrollment
CREATE TABLE enrollment (
  enroll_id  INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_id  INTEGER NOT NULL,
  semester   TEXT    NOT NULL,
  grade      TEXT,
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id)  REFERENCES course(course_id),
  UNIQUE (student_id, course_id, semester)
);
  • The composite UNIQUE constraint keeps us from inserting the same student/course/semester combination twice.
  • grade allows NULL for in-progress courses.

Type Selection Guidelines​

  • Identifiers: INTEGER (or BIGINT for very large systems) pairs well with auto-incrementing keys.
  • Codes: Use TEXT with validation rules (CHECK (course_code GLOB '[A-Z][A-Z][0-9][0-9][0-9]')) if your engine supports pattern checks.
  • Dates: SQLite stores dates as text; in engines with native date types prefer DATE, TIME, TIMESTAMP.
  • Booleans: SQLite represents booleans as integers (0/1). Other engines have native BOOLEAN.
  • Monetary values: Use DECIMAL(precision, scale) in systems that support it to avoid floating point rounding.

Adding Constraints After the Fact​

Suppose we add an advisor table and later realize emails must be unique:

ALTER TABLE advisor
ADD CONSTRAINT advisor_email_unique UNIQUE (email);

Before running this statement, verify no duplicate emails exist:

SELECT email, COUNT(*)
FROM advisor
GROUP BY email
HAVING COUNT(*) > 1;

Guardrails with CHECK​

CHECK constraints help encode business rules that don't fit other constraint types.

Validate semester format
ALTER TABLE enrollment
ADD CONSTRAINT semester_format CHECK (
  semester LIKE 'Spring %' OR semester LIKE 'Fall %'
);

SQLite evaluates CHECK clauses on every insert or update, rejecting rows that violate the condition.

Common Mistakes​

  • Wrong size: Using TEXT for numeric data prevents range checks and sorting from working as expected.
  • Missing NOT NULL: Required columns (like course_title) should disallow empty values.
  • Skipping foreign keys: Without them, orphaned rows accumulate (e.g., enrollments pointing to deleted courses).
  • Overly permissive codes: If course_code isn't constrained, typos like CSO1O (letter O vs zero) slip through.
  • Poorly named constraints: Name important constraints (e.g., UNIQUE (student_id, course_id, semester)) for easier debugging.

Summary​

  • Data types express how values are stored; choose ones that mirror the domain (integers for IDs, checked integers for credits, text for names).
  • Constraints protect data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK each cover a different rule.
  • Review the provided DDL whenever you extend the schema to keep consistency.

Quiz​

Show quiz

  1. Which constraint keeps the same student from enrolling twice in the same course and semester?
    A) PRIMARY KEY
    B) UNIQUE (student_id, course_id, semester)
    C) CHECK (credits > 0)
    D) FOREIGN KEY

  2. Why is dept_id nullable in the student table?
    A) Departments can be deleted
    B) Some students may be undeclared
    C) Foreign keys cannot be required
    D) It improves performance

  3. Which data type would you use for credits in a database that supports decimals, and why?
    A) TEXT to allow any value
    B) DECIMAL so partial credits are allowed
    C) INTEGER because credits are whole numbers
    D) REAL for floating point precision

  4. True or False: CHECK (credits <= 6) runs every time a row is inserted or updated.
    A) True
    B) False

Answers

  1. B
  2. B
  3. C
  4. A