Surrogate Key in RDBMS

A Surrogate Key is a system-generated unique identifier in a database that has no actual business meaning. Its only purpose is to uniquely identify each row in a table. Common examples include auto-increment integers, GUIDs, and system-generated codes.

Surrogate Key vs Natural Key

Unlike a natural key (like Student_ID or Email) which has real-world meaning, a surrogate key is purely artificial − it exists only to serve as a unique identifier for data management and analysis purposes.

Example

In the following ProductPrice table, the Key column is a surrogate key − it has no business meaning and is only used to uniquely identify each row ?

The surrogate key values (505_92, 698_56, etc.) carry no product information − they exist solely to uniquely identify each record.

Common Types of Surrogate Keys

• Auto-increment integer − Most common type (1, 2, 3, ...)
• GUID/UUID − Globally unique identifier (e.g., 550e8400-e29b-41d4-a716-446655440000)
• System date/time stamp − Generated from system clock
• Random alphanumeric string − System-generated codes

SQL Example

The following SQL creates a table with an auto-increment surrogate key ?

CREATE TABLE ProductPrice (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ProductID INT,
    Price DECIMAL(10, 2)
);

INSERT INTO ProductPrice (ProductID, Price) VALUES (1987, 200);
INSERT INTO ProductPrice (ProductID, Price) VALUES (1256, 170);
-- id is automatically generated: 1, 2, ...

Conclusion

A surrogate key is a system-generated identifier with no business meaning, used purely to uniquely identify rows. It is preferred when natural keys are too complex, composite, or subject to change, ensuring stable and efficient row identification.