In relational database, a key is one or more attributes (columns/fields) that:

  • Uniquely identify each row in a table (relation) — ensuring entity integrity (no duplicate rows).
  • Establish and enforce relationships between tables — ensuring referential integrity (valid references between related data).

More concretely: a key gives each row a (potentially) unique “identity” and enables connecting data across tables.

Without keys, tables would have no guaranteed way to distinguish between duplicate-looking rows or to safely relate rows across different tables — leading to ambiguity, redundancy, and poor data integrity.

However: “key” does not always mean “unique identifier among all keys”. Whether a key ensures uniqueness depends on which type of key it is.

1. Major Types of Keys

Here are the principal key types used in relational databases,

1. Super Key

A Super Key is a set of one or more columns that can uniquely identify each row in a table. It may contain extra columns that are not necessary for uniqueness.

Key Points:

  1. Can consist of one or multiple columns.
  2. Must uniquely identify each row.
  3. May contain extra columns, making it non-minimal.
  4. A Candidate Key = Minimal Super Key.
  5. Primary and alternate keys are subsets of super keys.

Example:

CREATE TABLE Employees (
    EmployeeID INT,                
    Email VARCHAR(100) NOT NULL,   
    NationalID VARCHAR(20) NOT NULL,
    Name VARCHAR(100)
);

-- Examples of Super Keys
-- EmployeeID alone
-- Email alone
-- NationalID alone
-- Combination: (EmployeeID, Email)
-- Combination: (EmployeeID, Name)

Key Examples from Code:

  • (EmployeeID)
  • (Email)
  • (NationalID)
  • (EmployeeID, Email) – still unique, but not minimal

Relationship Summary:

  • Super Key → Candidate Key → Primary/Alternate Key
  • Candidate Keys are minimal super keys
  • All candidate keys are super keys

2. Candidate Key

  • A Candidate Key is a minimal set of columns that can uniquely identify each row.
  • It is a Super Key without extra attributes.

Key Points:

  1. Must uniquely identify each row.
  2. Must be minimal.
  3. A table can have multiple candidate keys.
  4. One candidate key becomes the Primary Key.
  5. Others become Alternate Keys.

Example: SQL Code with Comments

CREATE TABLE Employees (
    EmployeeID INT,                
    Email VARCHAR(100) NOT NULL,   
    NationalID VARCHAR(20) NOT NULL
);

-- Primary Key
ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);

-- Alternate Keys (other candidate keys)
ALTER TABLE Employees
ADD UNIQUE (Email);
ALTER TABLE Employees
ADD UNIQUE (NationalID);

Key Examples from Code:

  • Candidate Keys: EmployeeID, Email, NationalID
  • Primary Key: EmployeeID
  • Alternate Keys: Email, NationalID

Relationship Summary:

  • Candidate Key = Minimal Super Key
  • Primary Key = chosen candidate key
  • Alternate Keys = other candidate keys
  • All candidate and alternate keys are unique keys

3. Primary Key

A Primary Key is a column or set of columns chosen from candidate keys to uniquely identify each row in the table.

Key Points:

  1. Must be unique and not null.
  2. Only one primary key per table.
  3. Can be a single column or composite (multi-column).
  4. Used for referencing in foreign keys.

Example: SQL Code with Comments

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,    -- Primary Key uniquely identifies each row
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    DepartmentID INT
);

Key Examples from Code:

  • Primary Key: EmployeeID

Relationship Summary:

  • Primary Key is a candidate key chosen for uniqueness
  • Other candidate keys become alternate keys
  • Can be referenced by foreign keys

4. Alternate Key

Definition: An Alternate Key is a candidate key that was not chosen as the primary key but can still uniquely identify rows.

Key Points:

  1. Must uniquely identify rows.
  2. Cannot be the primary key.
  3. Usually enforced using UNIQUE constraints.

Example: SQL Code with Comments

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) NOT NULL,    -- Alternate Key
    NationalID VARCHAR(20) NOT NULL -- Alternate Key
);

-- Enforce uniqueness for alternate keys
ALTER TABLE Employees
ADD UNIQUE (Email);
ALTER TABLE Employees
ADD UNIQUE (NationalID);

Key Examples from Code:

  • Alternate Keys: Email, NationalID

Relationship Summary:

  • Alternate Keys = Candidate Keys not chosen as Primary Key
  • All alternate keys are unique keys

5. Unique Key

A Unique Key is a constraint that ensures all values in a column or combination of columns are unique.

Key Points:

  1. Ensures no duplicate values.
  2. Can exist in addition to primary key.
  3. Can allow one NULL value (depending on DBMS).
  4. Can be single-column or multi-column.

Example: SQL Code with Comments

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,      -- Unique Key
    PhoneNumber VARCHAR(15) UNIQUE  -- Another Unique Key
);

Key Examples from Code:

  • Unique Keys: Email, PhoneNumber

Relationship Summary:

  • Unique Key can be an Alternate Key
  • Primary Key is always unique
  • Can exist independently of candidate/alternate keys

6. Foreign Key

A Foreign Key is a column or set of columns in one table that references the primary key of another table, enforcing referential integrity.

Key Points:

  1. Ensures referential integrity between tables.
  2. Values must exist in the referenced table.
  3. Can have cascading actions (ON DELETE/UPDATE).

Example: SQL Code with Comments

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Key Examples from Code:

  • Foreign Key: DepartmentID in Employees references DepartmentID in Departments

Relationship Summary:

  • Foreign Key points to a Primary Key in another table
  • Used to maintain relationships between tables

7. Composite Key

  • A Composite Key is a primary key made of two or more columns.
  • It uniquely identifies rows when a single column cannot.

Key Points:

  1. Must uniquely identify each row.
  2. Composed of multiple columns.
  3. Often used in junction/many-to-many tables.

Example: SQL Code with Comments

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    AssignedDate DATE,
    PRIMARY KEY (EmployeeID, ProjectID) -- Composite Key
);

Key Examples from Code:

  • Composite Key: (EmployeeID, ProjectID)

Relationship Summary:

  • Composite Key = Multi-column Primary Key
  • Ensures uniqueness across the combination of columns
  • Can be referenced by foreign keys in other tables

Ah, yes! You’re right — we haven’t covered Surrogate Key yet. Let’s add it in the same detailed format as the other keys.

8. Surrogate Key

  • A Surrogate Key is a system-generated unique identifier for a row, often used as a primary key instead of a natural candidate key.
  • It has no business meaning and exists purely to identify rows uniquely.

Key Points:

  1. Typically an auto-increment number or UUID.
  2. Has no real-world meaning (unlike natural keys such as Email or NationalID).
  3. Often used when natural keys are long, complex, or composite.
  4. Helps maintain simplicity and consistency in database relationships.
  5. Can still be used in foreign key references.

Example: SQL Code with Comments

CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY, -- Surrogate Key: system-generated unique ID
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,                 -- Natural key
    NationalID VARCHAR(20) UNIQUE             -- Natural key
);
  • Here, EmployeeID is a Surrogate Key:
    • Auto-incremented, system-generated, no business meaning.
    • Ensures every employee has a unique identifier.
  • Email and NationalID are natural candidate keys (can also serve as alternate keys).

Key Examples from Code:

  • Surrogate Key: EmployeeID
  • Candidate Keys (natural): Email, NationalID
  • Primary Key: EmployeeID (surrogate key chosen over natural keys)

Relationship Summary:

  • Surrogate Key is a special type of primary key.
  • It replaces natural candidate keys when simpler or system-generated keys are preferred.
  • Can be referenced by foreign keys in other tables.
  • Alternate keys (natural candidate keys) can still exist for uniqueness constraints.

Further Readings: