What is natural key?

In database design, a natural key is one or more columns in a table that uniquely identify each row without the need for an artificially generated identifier. These keys are derived from the business domain and inherently represent a unique characteristic of the entity.

What is a Natural Key?

A natural key, also known as a business key, is an attribute or a set of attributes that exists and is meaningful in the real world, and can uniquely identify a record in a database table. Unlike a surrogate key (an artificial identifier with no business meaning), a natural key uses data that is already part of the entity's properties.

For example, an ISBN for a book, a Social Security Number (SSN) for an individual (in some contexts), or an email address for a user (if uniqueness is enforced) could serve as natural keys. They are chosen because they are expected to be stable, unique, and non-nullable over the lifespan of the record.

Characteristics and Considerations

• Meaningful: It has intrinsic meaning in the business domain.
• Unique: It must uniquely identify each record in the table.
• Stable: Its value should not change frequently over time.
• Non-nullable: It should not contain NULL values.
• Natural vs. Surrogate: Often contrasted with surrogate keys (e.g., auto-incrementing integers) which have no business meaning.

Advantages of Natural Keys

• Business Relevance: Directly reflects real-world identifiers, making data interpretation intuitive.
• Data Integrity: Helps prevent duplicate real-world entities from being entered into the system.
• No Joins for Lookups: Often, you can query by the natural key directly without needing to join to an intermediary table if the key is already known outside the system.
• Portability: Data is more portable between systems as the key has intrinsic meaning.

Disadvantages of Natural Keys

• Volatility: If the natural key's real-world value changes, it requires updates across all referencing tables, which can be complex and expensive.
• Complexity: Natural keys can sometimes be composite (made of multiple columns), making indexing and foreign key definitions more cumbersome.
• Uniqueness Enforcement: Ensuring uniqueness can sometimes be challenging, especially for attributes that might seem unique but aren't always (e.g., names).
• Data Type: Can be larger (e.g., VARCHAR) than simple integer surrogate keys, potentially impacting performance and storage.
• Privacy Concerns: Using sensitive data (like SSN) as a primary key can raise privacy and security issues.

Example

Consider a Products table where each product has a unique SKU (Stock Keeping Unit). The SKU is assigned by the business, is guaranteed to be unique, and is used in the real world to identify products. It makes a good candidate for a natural key.

CREATE TABLE Products (
    SKU VARCHAR(50) PRIMARY KEY, -- Natural Key
    ProductName VARCHAR(255) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    SKU VARCHAR(50) NOT NULL, -- Foreign Key referencing Products.SKU
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (SKU) REFERENCES Products(SKU)
);

Conclusion

The choice between using a natural key or a surrogate key is a fundamental decision in database design. While natural keys offer business relevance and direct integrity, their potential for volatility and complexity often leads designers to prefer surrogate keys for primary identification, while still enforcing uniqueness on natural key candidates with unique constraints for business logic.