Surrogate Key in SQL:⚡

Many of you must know about Primary Key, Foreign Key , Unique Key etc.😃

But What is #Surrogate Key?🤔

✳️A Surrogate Key is just a unique identifier for each row and it may use as a Primary Key.

✳️ There is only requirement for a surrogate Primary Key, which is that each row must have a unique value for that column.

✳️A Surrogate Key is also known as an artificial key or identity key. It can be used in data warehouses.

✳️There is no business meaning for Surrogate Keys.

✳️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.

🎯A Surrogate Key should have the following characteristics:

▪️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.

🌟Note that Surrogate Keys are never used with any business logic other than simple Create, Read, Update and Delete (CRUD) operations.

❄️Example of Surrogate Key

-Identity Column in SQL Server

-GUID (Globally Unique Identifier)

-UUID (Universally Unique Identifier)

=====================================

🎯Advantages of Surrogate Key

⏩A Surrogate Key does not change so the application cannot lose their reference row in the database.

⏩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.

⏩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.

⏩ Surrogate Keys. It is very simple to implement them over the composite keys.

⏩It allows for a higher degree of normalization, so data is not duplicated within the database.

=====================================

🎯Disadvantages of Surrogate Key:

⏩Additional index is required.

⏩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.

⏩There is always a requirement to join to the main table when data is selected from a child table.

⏩It increases the sequential number by a random amount.

⏩There is some administrative overhand to maintain a Surrogate Key.

⏩Extra disk space required to store a Surrogate Key.