Constraints & Keys

Intermediate Databases
PRIMARY KEYFOREIGN KEYUNIQUECHECKNOT NULLDEFAULT

Objective

Enforce data integrity with database constraints and understand how they affect DML operations.

Tools & Technologies

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL
  • DEFAULT

Key Commands

PRIMARY KEY
FOREIGN KEY REFERENCES
UNIQUE
CHECK (age > 0)
ALTER TABLE ADD CONSTRAINT

Architecture Overview

graph TD TABLE[Table Row\nbeing inserted] --> PK{PRIMARY KEY\nunique + not null} PK -->|duplicate| REJECT1[Rejected] PK -->|ok| FK{FOREIGN KEY\nreferenced row exists?} FK -->|no match| REJECT2[Rejected] FK -->|ok| UNIQUE{UNIQUE constraint\nno duplicate?} UNIQUE -->|duplicate| REJECT3[Rejected] UNIQUE -->|ok| CHECK{CHECK constraint\nbusiness rule?} CHECK -->|fails| REJECT4[Rejected] CHECK -->|passes| ACCEPT[Row inserted] style ACCEPT fill:#1a1a2e,stroke:#00ff88,color:#e0e0e0 style REJECT1 fill:#1a1a2e,stroke:#ff4444,color:#ff4444

Step-by-Step Process

01
Define Constraints

Add integrity constraints at table creation or via ALTER TABLE.

-- Composite primary key
CREATE TABLE enrollment (
  student_id INT,
  course_id INT,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Check constraint
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price > 0),
  stock INT DEFAULT 0 CHECK (stock >= 0)
);

Challenges & Solutions

  • Constraint violation errors in application code indicate data layer bugs
  • CHECK constraints not supported in MySQL 5.7 — upgraded to 8.0+ for full support

Key Takeaways

  • Database constraints are your last line of defense against bad data
  • Name your constraints explicitly for meaningful error messages