Database Normalisation Questions Medium
A primary key is a column or a set of columns in a database table that uniquely identifies each record in the table. It is used to enforce entity integrity and ensure that there are no duplicate or null values in the primary key column(s). The primary key is typically chosen from the existing data in the table, such as a natural key (e.g., a social security number in a person table).
On the other hand, a surrogate key is an artificially generated key that is used as a substitute for the natural key in a database table. It is typically an auto-incrementing integer value assigned by the database management system (DBMS) when a new record is inserted. Surrogate keys have no meaning or significance outside of the database and are solely used for internal identification purposes.
The main difference between a surrogate key and a primary key is that a primary key is based on the actual data in the table and has some inherent meaning or significance, while a surrogate key is a system-generated identifier with no inherent meaning. Surrogate keys are often used in situations where the natural key is either too complex, subject to change, or not available. They provide a simple and efficient way to uniquely identify records in a table, especially in cases where the primary key needs to be hidden or protected for security or privacy reasons.
In summary, a primary key is based on the actual data in the table and has inherent meaning, while a surrogate key is a system-generated identifier with no inherent meaning and is used as a substitute for the natural key.