Microsoft SQL Server

A Surrogate Key is an artificial key generated by the database system to uniquely identify each record in a table. Unlike natural or business keys, a surrogate key has no real-world meaning and is only used within the database for internal identification. It is usually an auto-incrementing number or a system-generated unique identifier (GUID). Surrogate keys are useful when there is no natural or business key available, or when using a natural key would not be efficient.

Key Concepts of Surrogate Key:

1. Generated by the System

   • Surrogate keys are not derived from any existing data in the real world. They are automatically generated by the database system, often as integers that auto-increment.
   • For example, CustomerID can be a surrogate key that uniquely identifies each customer in a Customers table.

   Example SQL:

   CREATE TABLE Customers (
       CustomerID INT IDENTITY(1,1) PRIMARY KEY,  -- Surrogate Key
       CustomerName VARCHAR(100),
       ContactNumber VARCHAR(15)
   );

2. No Business Meaning

   • A surrogate key is only used to identify a record within the database and does not hold any business or real-world significance. Unlike a business key (like a product code), a surrogate key is purely for internal reference.

   Example SQL:

   INSERT INTO Customers (CustomerName, ContactNumber)
   VALUES ('John Doe', '123-456-7890');

3. Auto-Increment Feature

   • In Microsoft SQL Server, the IDENTITY keyword is used to create an auto-incrementing surrogate key. This ensures that every time a new record is added, a unique key is automatically generated.

   Example SQL (auto-increment surrogate key):

   CREATE TABLE Products (
       ProductID INT IDENTITY(1,1) PRIMARY KEY,  -- Surrogate Key
       ProductName VARCHAR(100),
       Price DECIMAL(10, 2)
   );

4. Independent from Business Changes

   • A surrogate key is useful because it does not change even if business data (like product name or customer details) changes. This ensures data consistency and reduces complications during updates.

   Example SQL (product-related):

   INSERT INTO Products (ProductName, Price)
   VALUES ('Laptop', 1200.00);

5. Use in Relationships

   • Surrogate keys are commonly used to establish relationships between tables, especially when a natural key is not available or practical. They simplify foreign key relationships and can be easily linked across tables.

   Example SQL (establishing a foreign key relationship):

   CREATE TABLE Sales (
       SaleID INT IDENTITY(1,1) PRIMARY KEY,
       CustomerID INT,
       ProductID INT,
       SaleAmount DECIMAL(10, 2),
       FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
       FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
   );

6. No Need for Natural Uniqueness

   • Surrogate keys are particularly useful when no natural key is guaranteed to be unique. For instance, if customer names can be repeated, the surrogate key provides a guaranteed unique identifier.

   Example SQL (handling non-unique business data):

   INSERT INTO Customers (CustomerName, ContactNumber)
   VALUES ('Jane Doe', '123-456-7890'), 
          ('Jane Doe', '555-987-6543');  -- Two different customers with the same name

Using a Surrogate Key is a common and efficient approach in database design, as it simplifies identification and relationships between records without relying on the actual business data for uniqueness.

Definition and Characteristics

Synthetic Origin:

Surrogate keys are artificially generated, meaning they are not derived from the application data. They are usually numeric and often implemented as an auto-incrementing number or a globally unique identifier (GUID).

Uniqueness:

Like all primary keys, a surrogate key uniquely identifies each row in a table. No two rows can have the same surrogate key value.

Stability:

Once assigned, the value of a surrogate key does not change. This stability is crucial for maintaining referential integrity across tables.

Non-significance:

Surrogate keys have no intrinsic meaning. They do not carry information about the row's data, unlike natural keys, which are derived from application data and might convey meaning.

Advantages

• Simplicity:Using an auto-incrementing number as a surrogate key simplifies the creation and management of keys. It eliminates the need to determine which natural data attributes can serve as a unique identifier.
• Performance:Surrogate keys can improve database performance, especially in joins, indexing, and query speed, because of their simplicity and uniformity.
• Flexibility:They allow for changes in business rules without affecting the primary key. For instance, if the natural key changes (e.g., a product code is updated), it doesn't require modifying the primary key in the database.
• Referential Integrity:Surrogate keys aid in maintaining referential integrity, as they are stable and unaffected by changes in business data.

Use Cases

• Dimensional Modeling:In data warehousing and dimensional modeling, surrogate keys are extensively used to uniquely identify dimension members, making it easier to track historical changes in dimension attributes.
• Complex Natural Keys:When natural keys are complex (composed of multiple columns or prone to change), surrogate keys offer a simplified and stable alternative.
• Support for ORM:Object-Relational Mapping (ORM) tools often work better with surrogate keys, as they can automatically generate and manage these keys, simplifying the development process.

Considerations

• Overhead:Introducing an additional column for the surrogate key adds some overhead to the database schema. However, the benefits often outweigh this drawback.
• Misuse:Surrogate keys should not be used as a substitute for understanding and modeling real-world relationships. They are a technical solution to a technical problem, not a replacement for good database design.

Conclusion

Surrogate keys are an essential tool in database design, offering a simple, stable, and efficient means of uniquely identifying rows in a table. They are particularly useful in scenarios where natural keys are complex, subject to change, or unsuitable for some reason. While surrogate keys add a layer of abstraction over natural data, they facilitate better performance, flexibility, and integrity in database management and design.

SURROGATE KEY DATA MODEL

• Designated Surrogate Keys: The department ID from the department table and the employee ID from the employee table are designated as surrogate keys.
• No Inherent Business Meaning: Their primary function is to serve as primary keys without carrying any inherent significance in actual business processes.
• Foreign Key References: Surrogate keys are employed as primary keys and are subsequently referenced in related tables as foreign keys.
• Internal System Values: In practical business scenarios, referencing an employee ID like "32" wouldn't represent an actual individual; such values are strictly internal to the database system.

SURROGATE KEY DATA

• Employee & Department Link: Consider an employee identified by the employee number EMP-01, who has been linked to department ID = 2.
• Department Lookup: Referencing this value in the department table aligns with the Executive Department, indicating that EMP-01 is affiliated with the executive department.
• Auto-Incrementing Key: The department ID in the department table functions as an auto-incrementing surrogate primary key.