Different Types of SQL Keys

Introduction

In relational database management systems (RDBMS), keys play a crucial role in ensuring data integrity and establishing relationships between tables. SQL keys help uniquely identify records, enforce constraints, and maintain database consistency. This blog explores the different types of SQL keys and their significance in database management.

1. Primary Key

A Primary Key uniquely identifies each record in a table and ensures that no two rows have the same key value.

Characteristics:

• Uniquely identifies each record in the table.
• It cannot have NULL values.
• A table can have only one primary key.
• Ensures uniqueness and data integrity.

Example:
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

2. Foreign Key

A Foreign Key establishes a relationship between two tables by referring to the Primary Key of another table.

Characteristics:

• Links two tables together.
• Ensures referential integrity by preventing invalid data references.
• Can accept NULL values if not explicitly restricted.
• A table can have multiple foreign keys.

Example:
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

3. Candidate Key

A Candidate Key is a column or combination of columns that can uniquely identify a row. One candidate key is selected as the Primary Key.

Characteristics:

• Uniquely identifies each record.
• A table can have multiple candidate keys.
• One candidate key becomes the Primary Key, others remain Alternate Keys.
• Cannot contain NULL values.

Example:
CREATE TABLE Employees (
    EmployeeID INT UNIQUE,
    Email VARCHAR(100) UNIQUE,
    PhoneNumber VARCHAR(15) UNIQUE
);

4. Super Key

A Super Key is a set of one or more columns that uniquely identifies a row. It includes Candidate Keys and may have extra attributes.

Characteristics:

• A superset of Candidate Keys.
• Can include extra attributes beyond uniqueness requirements.
• Every Candidate Key is a Super Key, but not vice versa.

Example:
(EmployeeID, Email, PhoneNumber) → Super Key
(EmployeeID) → Candidate Key

5. Composite Key

A Composite Key consists of two or more columns that together uniquely identify a record.

Characteristics:

• Formed by two or more columns.
• Used when no single column can uniquely identify a row.
• Common in many-to-many relationships.

Example:
CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);

6. Unique Key

A Unique Key ensures that the column values remain distinct but allows NULL values.

Characteristics:

• Ensures uniqueness across all records.
• Allows one NULL value per column.
• A table can have multiple Unique Keys.

Example:
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    SerialNumber VARCHAR(50) UNIQUE
);

7. Alternate Key

An Alternate Key is a Candidate Key that is not chosen as the Primary Key.

Characteristics:

• A Candidate Key that is not the Primary Key.
• Can be used as an alternative identifier.

Example:
If EmployeeID is chosen as the Primary Key, then Email becomes an Alternate Key in the Employees table.

8. Surrogate Key

A Surrogate Key is an artificially generated key, usually a numeric value, that substitutes for a natural key.

Characteristics:

• Not derived from actual data.
• Automatically generated using AUTO_INCREMENT.
• Simplifies database relationships.

Example:
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    OrderNumber VARCHAR(20) UNIQUE
);

Conclusion

SQL keys are fundamental in database design. They ensure data integrity, uniqueness, and efficient data retrieval. The appropriate key type depends on the specific use case.

Key Takeaways:

• Primary Key: Uniquely identifies records.
• Foreign Key: Establishes relationships.
• Candidate Key: Potential primary keys.
• Super Key: A superset of candidate keys.
• Composite Key: Multiple columns combined as a unique identifier.
• Unique Key: Similar to a primary key but allows NULL.
• Alternate Key: A candidate key not chosen as the primary key.
• Surrogate Key: System-generated unique identifier.

Understanding and implementing these keys correctly ensures a well-structured, optimized, and error-free database. 🚀