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

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

By Ravindra Bagale Last updated: 15 October 2026

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.

Extended teaching notes

Why this lesson matters

Use this lesson to strengthen systems you own: understand the risk, practise safely in a lab, then write and apply the fix.

Apply the fix across stacks

Same class of bug, same idea in every language.

Java / Spring Boot

Parameterise queries, encode output, CSRF tokens, limited Actuator, dependency scanning.

ASP.NET Core

EF Core / parameterised SQL, Razor encoding, HTTPS, Data Protection.

Node.js / Express

helmet, parameterised SQL, npm audit, avoid eval and shell concat.

Python / Django / Flask / FastAPI

ORM, autoescape, pip-audit, Bandit; never shell=True with user input.

PHP / Laravel

PDO/Eloquent, Blade escaping, composer audit.

Go / Ruby

database/sql placeholders / ActiveRecord; html/template / ERB escaping; govulncheck / Brakeman.

nginx / Apache / Tomcat

Hide versions, TLS, security headers, least-open ports.

MySQL / PostgreSQL / MongoDB / Redis

Private networks, auth on, least-privilege users, no 0.0.0.0/0.

AWS / Azure / GCP

Roles not long-lived keys, private data stores, cloud audit logs on.

Mini case study

Suppose we are on a purple-team exercise for an Indian mid-size company (sample data). Red notes a technique from this lesson against a lab clone. Blue must show the control that stops it, the log that would have fired, and the ticket that hardens production. That is the standard this course trains you for.

Defender's worksheet (do this today)

Write the lesson title, date and the exact lab target you are allowed to use.
List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
Run one safe check from the lesson on a lab system only. Paste the command and output.
Write a two-sentence fix you would give a teammate.
Name one related public advisory (CERT-In, CISA, NVD, or a vendor bulletin) with its date.

Indian workplace (sample data)

Suppose we are a junior analyst at Infosys, TCS, Wipro or a fintech SOC in Bengaluru/Pune. Turn this lesson into one checklist item with an owner, a due date and a place to store evidence (ticket + screenshot).

Global practice

Compare your notes with OWASP, CIS Benchmarks, NIST CSF/IR guidance or a cloud Well-Architected security question. Write five lines on what overlaps.

Everyday analogy

Explain the risk and the fix to a family member without jargon. If they can repeat the warning, you understand it well enough to teach juniors.

Key terms

Control: a safeguard (patch, config, process, detection).
Finding: a weakness backed by evidence.
Remediation: the fix plus how you verified it.
Scope: systems you have written permission to touch.
Least privilege: only the access the task needs.
Defence in depth: several layers so one failure does not end the story.

Common mistakes

Skipping written permission "just for a quick scan".
Practising on hostel, office or public targets without authorisation.
Collecting tool output without writing the fix.
Copying attack steps onto production to "verify". Defenders verify with versions, configs and patches.
Publishing screenshots that contain secrets, cookies or internal hostnames.

Recap

Evidence, fix, owner, date.
Own lab only.
Fix > spectacle.