What is Surrogate Key in SQL?

Most of us know about Primary Key, Foreign Key, Unique Key etc.😃

But What is Surrogate Key?🤔

1. A Surrogate Key is just a unique identifier for each row, and it may use as a Primary Key.
2. There is only requirement for a surrogate Primary Key, which is that each row must have a unique value for that column.
3. A Surrogate Key is also known as an artificial key or identity key. It can be used in data warehouses.
4. There is no business meaning for Surrogate Keys.
5. This type of key is either database generated or generated via another application (not supplied by user).

🌟There is a slight difference between a Surrogate Key and a Primary Key. Ideally, every row has both a Primary Key and a Surrogate Key.

🌟 The Primary Key identifies the unique row in the database, while the Surrogate Key identifies a unique entity in the model.

Characteristics of Surrogate Key:

• Unique Value
• The key is generated by the system, in other words automatically generated
• The key is not visible to the user (not a part of the application)
• It is not composed of multiple keys
• There is no semantic meaning of the key.

Examples of Surrogate Key

• Identity Column in SQL Server
• GUID (Globally Unique Identifier)
• UUID (Universally Unique Identifier)

Advantages of Surrogate Key:

1. A Surrogate Key does not change, so the application cannot lose their reference row in the database.
2. If the Primary Key is changed, then the related foreign key does not change across the database because the Surrogate Key is used as a reference key. In other words, the Surrogate Key value is never changed, so the foreign key values become stable.
3. A Surrogate Key is most often a compact data type, such as an integer. A Surrogate Key is less expensive in a “Join” than the compound key.
4. Surrogate Keys. It is very simple to implement them over the composite keys.
5. It allows for a higher degree of normalization, so data is not duplicated within the database.

Disadvantages of Surrogate Key:

1. Additional index is required.
2. It cannot be used as a search key because it is not related to any business logic, or it is independent of any business logic.
3. There is always a requirement to join to the main table when data is selected from a child table.
4. It increases the sequential number by a random amount.
5. There is some administrative overhand to maintain a Surrogate Key.
6. Extra disk space required to store a Surrogate Key.