Primary Key Vs Surrogate key

In this blog, you will understand the difference between a primary key and a surrogate key.

Primary Key:

A primary key is a column or a set of columns in a table that uniquely identifies each record in the table. It ensures data integrity and serves as the reference point for establishing relationships with other tables.

Example:

CREATE TABLE Branch (
BranchID INT PRIMARY KEY,
BranchName VARCHAR(50),
Location VARCHAR(100)
);

In this example, BranchID is designated as the primary key, ensuring that each branch in the table is uniquely identified by its ID.

Surrogate key:

A surrogate key is an artificially generated unique identifier for each record in a table. Unlike natural keys, which are based on existing data attributes, surrogate keys are made just for the database’s internal use and don’t carry any real-world meaning.

In the provided example, Branch ID 1 appears twice with different locations:

1: Delhi Main Street Branch located at A Main St
2: Delhi Main Street Branch located at B Main St

By incorporating a surrogate key (SurrogateKey), each branch location is uniquely identified regardless of its natural attributes, like BranchID or BranchName. As a result, even though “Main Street Branch” appears twice in the dataset, each occurrence is assigned a distinct surrogate key.

This ensures that each branch location can be accurately referenced and managed within the database, preventing any ambiguity or confusion that may arise from duplicate names or locations.

Thank you for reading this post, and I hope you enjoyed it!