Constraints

Enforcing Data Quality

Constraints are rules that enforce data integrity. They prevent invalid data from being inserted into the database, ensuring your data remains accurate and consistent.

Types of Constraints

  Constraint         │ Purpose                  │ Example
  ───────────────────┼──────────────────────────┼──────────────────
  PRIMARY KEY        │ Unique row identifier    │ ID INT PRIMARY KEY
  FOREIGN KEY        │ Link to another table    │ DeptID REFERENCES Departments(ID)
  NOT NULL           │ Column cannot be NULL    │ Name VARCHAR(100) NOT NULL
  UNIQUE             │ All values must differ   │ Email VARCHAR(150) UNIQUE
  CHECK              │ Values must satisfy rule │ Age CHECK (Age >= 16)
  DEFAULT            │ Value if none specified  │ Status DEFAULT 'active'

PRIMARY KEY

  -- Single column primary key
  CREATE TABLE Students (
      ID INT PRIMARY KEY,
      Name VARCHAR(100)
  );

  -- Composite primary key
  CREATE TABLE Enrollments (
      StudentID INT,
      CourseID INT,
      Grade CHAR(2),
      PRIMARY KEY (StudentID, CourseID)
  );

FOREIGN KEY

  CREATE TABLE Enrollments (
      StudentID INT,
      CourseID INT,
      FOREIGN KEY (StudentID) REFERENCES Students(ID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
      FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
        ON DELETE RESTRICT
  );

  -- Referential actions:
  ON DELETE CASCADE    -- Delete child rows when parent is deleted
  ON DELETE RESTRICT   -- Prevent parent deletion if children exist
  ON DELETE SET NULL   -- Set foreign key to NULL when parent is deleted
  ON DELETE SET DEFAULT -- Set to default value

CHECK Constraint

  CREATE TABLE Products (
      ID INT PRIMARY KEY,
      Name VARCHAR(100) NOT NULL,
      Price DECIMAL(10,2) CHECK (Price > 0),
      Stock INT CHECK (Stock >= 0),
      Rating DECIMAL(3,2) CHECK (Rating BETWEEN 1.0 AND 5.0)
  );