11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users
11.5 Keys: Primary, Foreign, Unique, Composite, Candidate, Super, Alternate
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
Candidate, super aani alternate key madhe khup students gondhaltat. Soppa kram lakshat theva: super key (unique karnare kontehi set) → tyatle minimal te candidate → tyatla ek nivadla to primary → urlele te alternate. Ek table ghevun he bolun dakhva – ratun nako.
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.