Surrogate keys are auto incremented numbers that have no real world meaning. Auto incremented means that each row will have the next highest number.
Keep in mind that the one that you should use can vary depending upon the purpose of your database.
Surrogate key example
Surrogate primary key
SQL surrogate key
What is a surrogate key?
Surrogate key definition
Follow me:
[ Ссылка ]
[ Ссылка ]
[ Ссылка ]
Contact me:
munirhussain.du@gmail.com
Surrogate Key In Database Design
===========================
In database design, it is a good practice to have a primary key for each table. There are two ways to specify a primary key:
The first is to use part of the data as the primary key. For example, a table that includes information on employees may use Social Security Number as the primary key. This type of key is called a natural key.
The second is to use a new field with artificially-generated values whose sole purpose is to be used as a primary key. This is called a surrogate key.
A surrogate key has the following characteristics:
Surrogate key is a unique identification key, it is like an artificial or alternative key to production key, because the production key may be alphanumeric or composite key but the surrogate key is always single numeric key.
It is typically an integer.
Surrogate key is system generated artificial primary key values.
Surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.
It has no meaning. You will not be able to know the meaning of that row of data based on the surrogate key value.
It is not visible to end users. End users should not see a surrogate key in a report.
Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys.
It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.
Way to generate surrogate keys:
Surrogate keys can be generated in a variety of ways, and most databases offer ways to generate surrogate keys. For example, Oracle uses SEQUENCE, MySQL uses AUTO_INCREMENT, and SQL Server uses IDENTITY.
Use of surrogate keys:
Surrogate keys are often used in data warehousing systems, as the high data volume in a data warehouse means that optimizing query speed becomes important. Using a surrogate key is advantageous because it is quicker to join on a numeric field rather than a non-numeric field.
There are no hard rules on when to employ a surrogate key as opposed to using the natural key. Often the data architect would need to look at the nature of the data being modeled and stored and consider any possible performance implications. The following are examples of when it makes sense to use a surrogate key:
When different source systems use different keys for the same record. When we integrate the systems, instead of picking one set of keys, it is often better to use a surrogate key.
Thanks for linking, commenting, sharing and watching more of our videos
