Ravindra BagaleCourses & study guides

11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users

11.5 Keys: Primary, Foreign, Unique, Composite, Candidate, Super, Alternate

By Last updated:

Keys rows olakhayla aani tables jodayla vaparle jatat. Ha theory cha bhag aahe pan interview madhe khup vicharla jato – ek example ghevun sagle keys samjuya.

Key Meaning In our tables
Super key Any set of columns that uniquely identifies a row {student_id}, {email}, {student_id, name}
Candidate key A minimal super key (no extra column) {student_id}, {email}
Primary key The candidate key chosen as the main identifier; unique and NOT NULL, one per table student_id
Alternate key Candidate keys not chosen as primary email
Unique key Values must be unique; NULL allowed (MySQL allows many NULLs) email UNIQUE
Composite key A key made of two or more columns together (student_​id, course_​id)
Foreign key Column that refers to the primary key of another table enrollments.​student_​id → students.​student_​id 
-- composite primary key + two foreign keys, with actions
CREATE TABLE attendance (
  student_id INT,
  course_id  INT,
  class_date DATE,
  present    BOOLEAN DEFAULT TRUE,
  PRIMARY KEY (student_id, course_id, class_date),
  CONSTRAINT fk_att_student FOREIGN KEY (student_id)
      REFERENCES students(student_id) ON DELETE CASCADE,
  CONSTRAINT fk_att_course  FOREIGN KEY (course_id)
      REFERENCES courses(course_id)   ON DELETE RESTRICT
);

ALTER TABLE enrollments ADD CONSTRAINT uq_student_course UNIQUE (student_id, course_id);

INSERT INTO enrollments (student_id, course_id, score) VALUES (99, 1, 50);
-- ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
ON DELETE option When the parent row is deleted
RESTRICT / NO ACTION (default) Delete is refused while child rows exist
CASCADE Child rows are deleted too
SET NULL Child column becomes NULL

Ravindra Bagale's Tip

Many students get confused between candidate, super and alternate keys. Remember a simple order: super key (any set that makes rows unique) → the minimal ones among those are candidate keys → the one chosen from them is the primary key → the rest are alternate keys. Take one table and explain this out loud – don't memorise it.

Practice task

For a users table with user_id, username, email, phone, list the super, candidate, primary and alternate keys. Create the attendance table and test what happens when you delete a student who has attendance rows.