In DBMS, a key is an attribute (column) or a set of attributes that uniquely identifies each row in a table and helps connect tables together while maintaining data integrity.

Below is a friendly, exam-ready breakdown in mini-sections.

🔑 What is a Key in DBMS?

  • A key is a column or combination of columns used to:
    • Uniquely identify a record in a table.
    • Avoid duplicate data.
    • Link one table with another.
  • Without keys, a database would become confusing, inconsistent, and hard to search.

Think of a key like a unique ID card for each row in a table.

Why Are Keys Important?

Keys help a DBMS to:

  • Ensure uniqueness of records (no two students with the same unique ID).
  • Maintain data integrity (valid relationships between tables, fewer anomalies).
  • Support faster searches and queries , because many keys create indexes internally.
  • Define relationships (one-to-one, one-to-many, many-to-many) between tables.

Example:
A Students table might have StudentID as a key so that even if two students share the same name, the system still distinguishes them.

Main Types of Keys in DBMS

Below are the most commonly discussed key types in exams and interviews.

1. Super Key

  • Any set of attributes that can uniquely identify a row.
  • It may contain extra, unnecessary attributes.
  • Example: In Students(StudentID, Email, Name), these can be super keys:
    • {StudentID}
    • {Email}
    • {StudentID, Email} (still uniquely identifies, but not minimal)

2. Candidate Key

  • A minimal super key: uniquely identifies a row and has no redundant attributes.
  • A table can have multiple candidate keys.
  • Example:
    • StudentID and Email can both uniquely identify a student → both are candidate keys.

3. Primary Key

  • One chosen candidate key used as the main identifier of a row.
  • Properties:
    • Must be unique.
    • Cannot be NULL.
    • Only one primary key per table (though it can be made of multiple columns).
  • Examples:
    • StudentID in Students table.
    • OrderID in Orders table.

4. Alternate Key

  • Candidate keys that are not chosen as the primary key.
  • Example:
    • If StudentID is primary key and Email is also unique, then Email is an alternate key.

5. Foreign Key

  • A column (or set of columns) in one table that refers to the primary key of another table.
  • Used to create relationships and enforce referential integrity.
  • Example:
    • Students(StudentID, Name, DeptID)
    • Departments(DeptID, DeptName)
    • Here Students.DeptID is a foreign key referencing Departments.DeptID.

6. Composite (or Compound) Key

  • A key made up of more than one column.
  • Used when a single column is not enough to uniquely identify a record.
  • Example:
    • In Enrollment(StudentID, CourseID, EnrollmentDate), the combination (StudentID, CourseID) can be a composite primary key.

7. Unique Key

  • Ensures that all values in a column (or a set of columns) are unique.
  • Difference from primary key:
    • Unique key may allow one NULL (depending on DBMS), primary key cannot.
  • Example:
    • A Username field in a Users table may be defined as a unique key.

8. Surrogate Key

  • An artificial key, often an auto-generated number (like an identity column).
  • Has no business meaning; used purely for identification.
  • Example:
    • UserID as an auto-increment integer even if there is a natural key like Email.

HTML Table: Common DBMS Keys at a Glance

html

<table>
  <thead>
    <tr>
      <th>Key Type</th>
      <th>What It Does</th>
      <th>Example</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Super Key</td>
      <td>Any attribute set that uniquely identifies a row (may have extra attributes).[web:1][web:9]</td>
      <td>{StudentID}, {Email}, {StudentID, Email}</td>
    </tr>
    <tr>
      <td>Candidate Key</td>
      <td>Minimal super key; no redundant attributes.[web:1][web:3]</td>
      <td>StudentID, Email (if each is individually unique)</td>
    </tr>
    <tr>
      <td>Primary Key</td>
      <td>Main unique identifier for each row, no NULL, no duplicates.[web:1][web:6]</td>
      <td>StudentID in Students table</td>
    </tr>
    <tr>
      <td>Alternate Key</td>
      <td>Candidate keys not chosen as primary key.[web:3][web:5]</td>
      <td>Email when StudentID is primary key</td>
    </tr>
    <tr>
      <td>Foreign Key</td>
      <td>Refers to primary key of another table to create relationships.[web:1][web:6]</td>
      <td>DeptID in Students referencing Departments.DeptID</td>
    </tr>
    <tr>
      <td>Composite Key</td>
      <td>Key formed by multiple columns.[web:3][web:9]</td>
      <td>(StudentID, CourseID) in Enrollment table</td>
    </tr>
    <tr>
      <td>Unique Key</td>
      <td>Enforces uniqueness; may allow NULL depending on DBMS.[web:7]</td>
      <td>Username in Users table</td>
    </tr>
    <tr>
      <td>Surrogate Key</td>
      <td>Artificial, system-generated identifier without business meaning.[web:1][web:7]</td>
      <td>Auto-increment UserID</td>
    </tr>
  </tbody>
</table>

Short, Exam-Style Summary (TL;DR)

  • A key in DBMS is an attribute or set of attributes that uniquely identifies a row and helps connect tables.
  • Important types:
    • Super key, candidate key, primary key, alternate key, foreign key, composite key, unique key, surrogate key.
  • Primary key = main identifier, foreign key = link between tables, composite key = multiple columns used together, unique key = no duplicates, surrogate key = artificial ID.

Information gathered from public forums or data available on the internet and portrayed here.