As the name suggests Surrogate Key is a term which is used in DWH space to be used as a STAND-IN Key or GENERATED KEY for every unique record from the source system in order to preserve the natural key coming in from the source. Primary key is nothing but in a general database terms is a column or set of columns to used to identify a record(tuple) uniquely in a table.
In relational dbs, primary key is the column or a set of columns that identify uniquely a record of a table. Surrogate key in general, can be defined as primary key of a table as well. The main characteristic of surrogate key is that it has no meaning from the application perspective, because it has generated values. On the other hand it helps the modeler isolates complexity among DBs he's integrating in one centralized model or db.
A primary key is more of an business implementation. It has a business meaning. But a surrogate Key is more of a physical implementation to avoid duplicates while implementing SCD on Dimensional tables. Surrogate key is developer generated and Primary key is business derived.
Primary Key is a phisical concept related to a teble structure and represent the preferred way to be used to query the table. The surrogate key is a "logical" concept and indicates that the values of a key (primary or not) is generated from other columns of the table. In other words is a calculated key. In a DW built with Teradata in third normal form a surrogate key should never be used. (Based on various user's views from Linkedin.com)
Surrogate key is generated key for each record added to data warehouse table.
Primary keys are keys which are used in OLTP systems and they share some business meaning. Ex. Product key having some unique product information attached to it.
As the name suggests Surrogate Key is a term which is used in DWH space to be used as a STAND-IN Key or GENERATED KEY for every unique record from the source system in order to preserve the natural key coming in from the source. Primary key is nothing but in a general database terms is a column or set of columns to used to identify a record(tuple) uniquely in a table.
ReplyDeleteIn relational dbs, primary key is the column or a set of columns that identify uniquely a record of a table. Surrogate key in general, can be defined as primary key of a table as well. The main characteristic of surrogate key is that it has no meaning from the application perspective, because it has generated values. On the other hand it helps the modeler isolates complexity among DBs he's integrating in one centralized model or db.
ReplyDeleteA primary key is more of an business implementation. It has a business meaning. But a surrogate Key is more of a physical implementation to avoid duplicates while implementing SCD on Dimensional tables.
Surrogate key is developer generated and Primary key is business derived.
Primary Key is a phisical concept related to a teble structure and represent the preferred way to be used to query the table. The surrogate key is a "logical" concept and indicates that the values of a key (primary or not) is generated from other columns of the table. In other words is a calculated key. In a DW built with Teradata in third normal form a surrogate key should never be used. (Based on various user's views from Linkedin.com)
Surrogate key is generated key for each record added to data warehouse table.
ReplyDeletePrimary keys are keys which are used in OLTP systems and they share some business meaning. Ex. Product key having some unique product information attached to it.