Most of us 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.
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:
- 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.
Top comments (0)