Keys

👉 Okay so basically...

Let’s understand all the keys shown in your image with proper meaning, simple ERD-style examples, and how they are used in real-world database design. These are all part of database normalization and relational modeling.

1. Primary Key

🔑 The main unique identifier of a table.

Example:

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100)
);

In ERD:

student_id is underlined or marked with a key symbol.

2. Candidate Key

🔍 Any column (or combo) that can uniquely identify a row — before deciding the primary key.

Example:

In Students table:

student_id, email, and aadhar_number are all candidate keys.
We choose one as primary key.
Rest become alternate keys.

3. Alternate Key

🔄 Candidate key that was not chosen as the primary key.

Example:

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

In ERD:

student_id = Primary key
email = Alternate key (enforced with UNIQUE)

4. Foreign Key

🔗 A key in one table that points to a primary key in another table.

Example:

CREATE TABLE Enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

In ERD:

Arrow from Enrollments.student_id to Students.student_id.

5. Super Key

🛠️ Any set of columns that can uniquely identify a row. It includes candidate keys + extra attributes.

Example:

{student_id} → Super Key ✅
{student_id, name} → Super Key (but not minimal)

So every candidate key is a super key, but not vice versa.

6. Compound Key

🔗 A key made of two or more columns that together uniquely identify a row, but not necessarily from different tables*.*

Example:

CREATE TABLE Attendance (
  student_id INT,
  date DATE,
  status VARCHAR(10),
  PRIMARY KEY (student_id, date)
);

Here, student_id + date form a compound key.

7. Composite Key

🧩 Same as compound key — a combination of columns used as the primary key.

✅ In practice, composite key = compound key

8. Simple Key

🔹 A primary key based on a single column.

Example:

CREATE TABLE Courses (
  course_code VARCHAR(10) PRIMARY KEY,
  course_name VARCHAR(100)
);

9. Natural Key

🧬 A key that comes from real-world data.

Example:

Using email, SSN, or Aadhar number as the primary key.

CREATE TABLE Users (
  aadhar_number CHAR(12) PRIMARY KEY
);

✅ It's "natural" because it already exists outside the system.

10. Surrogate Key

🆔 A key created artificially — like an auto-incremented ID.

Example:

CREATE TABLE Orders (
  order_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(100)
);

ERD shows:

order_id as surrogate key
No meaning outside the DB

11. Intelligent Key

🧠 A key that holds encoded business logic (not always recommended).

Example:

Product code: PRD2025IN001
- PRD: product
- 2025: year
- IN: India
- 001: serial

Risk: Changes in business logic break this design — hard to maintain.

✅ Putting It All Together (Mini ERD Style)

Let’s say we design this:

📘 Students Table

Column	Type	Key Type
student_id	INT	Primary, Surrogate
email	VARCHAR	Alternate, Natural
roll_no	INT	Candidate

📘 Enrollments Table

Column	Type	Key Type
student_id	INT	Foreign
course_id	VARCHAR	Foreign
date	DATE	Part of Composite Key

Primary Key: (student_id, course_id, date) → Composite / Compound Key

💬 FAQs on These Keys

Question	Answer
Can a table have multiple candidate keys?	Yes
Is every primary key a super key?	Yes
Is every super key a candidate key?	No (because super key can have extra columns)
Should I use natural or surrogate keys?	Surrogate keys are safer, natural keys are meaningful but risky if data changes
Is composite key same as compound key?	Yes, mostly used interchangeably

Want me to generate a proper ERD diagram showing all these in one place? Just say and I’ll draw it for you.