Implement Surrogate Keys for Better Database Design

Designing a Surrogate Key for Entity Identification

Write a SQL query to design a surrogate key for entity identification.

Solution:

-- Table without a natural primary key.
CREATE TABLE Employees (
    SSN VARCHAR(11), -- Social Security Number (not ideal as a primary key).
    Name VARCHAR(100),
    Department VARCHAR(100)
);

-- Add a surrogate key for better identification.
ALTER TABLE Employees ADD EmployeeID INT IDENTITY(1,1) PRIMARY KEY;

Explanation:

• Purpose of the Query :
• The goal is to design a surrogate key to uniquely identify entities in a table.
• Key Components :
• EmployeeID: A surrogate key added to the table.
• Ensures uniqueness and stability of the primary key.
• Why Use Surrogate Keys? :
• Surrogate keys are independent of business logic and remain stable over time.
• They simplify relationships between tables.
• Real-World Application :
• In employee databases, surrogate keys avoid issues with changing SSNs or other identifiers.

Notes:

• Surrogate keys are typically auto-incremented integers.
• Use them when natural keys are unstable or overly complex.
• Important Considerations:
• Ensure proper indexing on surrogate keys.

For more Practice: Solve these Related Problems:

• Write a SQL query to add a surrogate key to a table storing customer details where the natural key is a combination of name and address.
• Write a SQL query to design a surrogate key for a table storing product details where the natural key is a complex composite key.
• Write a SQL query to add a surrogate key to a table storing employee details where the natural key is an unstable identifier like email.
• Write a SQL query to create a surrogate key for a table storing supplier details where the natural key is prone to changes.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Denormalizing for Performance Optimization.
Next SQL Exercise: Resolving Insertion Anomalies in Database Design.