✏️ Explanatory Question
Level: Basic — Keys are the backbone of relational databases; interviewers expect all major types.
A key is a column (or set of columns) used to uniquely identify rows and establish relationships between tables. MySQL supports several types of keys, each with a specific purpose.
| Feature | Primary Key | Unique Key | Foreign Key |
|---|---|---|---|
| Uniqueness | Yes | Yes | Can repeat |
| NULL allowed? | No | Yes (one NULL) | Yes |
| Number per table | Only one | Many | Many |
| Purpose | Identify each row | Prevent duplicates | Link tables |
CREATE TABLE departments (
dept_id INT PRIMARY KEY, -- Primary Key
dept_name VARCHAR(50) UNIQUE -- Unique Key
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY, -- Primary Key
email VARCHAR(100) UNIQUE, -- Unique Key (one NULL allowed)
dept_id INT,
-- Foreign Key linking to departments
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- Composite Key example (two columns together)
CREATE TABLE enrollment (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id) -- Composite Key
);