Keys Are The Key

Not going to win any awards for this blog post title. But keys really are a critical part of a database design and are often misunderstood, ignored and used in the wrong way. Keys are just attributes like everything other attribute, but they have a very special remit – they are the unique identifier for a row in data set. They’re much more than simple identifiers though. Keys establish relationships between tables, enforce data integrity and optimize query performance. They’re the foundation upon which relational (and other data formats) database architecture is built.

Keys have been with us for some time. And while the first usage of key in databases is difficult to pinpoint, Edgar F. Codd is credited with formalizing and popularizing the concepts of primary keys and foreign keys in his seminal 1970 paper that defined the relational database model (discussed in the post below).

Data Models 1970

You are already exposed to the concept of keys from the time you are born. Your name is a form of key (albeit not a very good one) that helps to try and uniquely identify you. Other keys emerge later in life like social security, passport and driving license numbers. These electronic keys are designed in a manner that will uniquely differentiate you from someone else. Probably.

In databases, we have a number of different types of keys that all serve similar purposes, but do so in slightly different manners. Let’s walk through a list of common key types and talk about why they exist, how we should use them and where they should be employed.

Primary Key (PK)

Most people will have heard of this and have a working understanding of what it means. The primary key is the star of the show. It uniquely identifies each record in a table and cannot contain null values. The null case should be obious as null is not equal to anything, nor is it unequal to anything. Every table should have exactly one primary key, whether it’s a single column or a combination of columns (composite key) that is discussed later.

Consider a customer table: a customer_id serves as the primary key, ensuring no two customers share the same identifier. This simple mechanism prevents duplicate records and provides a reliable reference point for other tables.

I state that every table should have a primary key and not mandate that every table must have one. And its worth taking some time to understand these cases. For bridge tables (many-to-many) where the combination of foreign keys naturally forms the relationship, and there’s no need for an additional surrogate key. Pusists may disagree and add one anyway, but its an example of where you may decide to not include one.

CREATE TABLE student_course (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Foreign Key (FK)

Foreign keys are the relationship builders of the database world. Again, these emerged from the work of Edgar F. Codd. They are used to reference primary keys in other tables, establishing connections between related data. A foreign key in an orders table pointing to the customer_id in a customers table creates the vital link showing which customer placed which order.

-- Parent table: customers
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Child table: orders
CREATE TABLE order (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2),
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

Foreign keys enforce referential integrity, ensuring you can’t create an order for a non-existent customer or delete a customer who has active orders (depending on your cascade rules – these are rules the database engine will enforce for you to prevent data corruption).

Composite Key (CK)

Composite keys are named because the key is composed of other keys. They exist becase a single key attribute alone is not sufficient to uniquely identify a record. A trivial example would be your name. Imagine a company with 100,000 employeed that had a table which stored user details and used your first name as the key. Obviously that would not be unique, unless you are Elon Musks son. It could be further improved by adding your surname, but again that would not be unique. Maybe then add you DOB. Better, but still not unique as there could (however unlikely it seems) be another person with the same name and birthday as you. In these examples it’s common to use natural (employee ID), or surrogate (random, automatically generated) keys to ensure uniqueness is enforced and is stabkle (it would not matter if you were married and changed your surname for example, as the natural/surrougate key remains the same). You get the point. Composite keys are there to compose a key based on multiple attrbutes that further help enforce uniqueness. The example below shows the composite key required to ensure the booking of a room in a hotel on a particular data is unique as it composes the hotel, 

-- Table: room_booking
CREATE TABLE room_booking (
    hotel_id INT NOT NULL,
    room_number VARCHAR(10) NOT NULL,
    booking_date DATE NOT NULL,
    guest_name VARCHAR(100) NOT NULL,
    check_in_time TIME,
    check_out_time TIME,
    PRIMARY KEY (hotel_id, room_number, booking_date)
);

Surrogate Key

As mentioned previously, surrogate keys are artificial identifiers created specifically to serve as primary keys, typically auto-incrementing integers or UUIDs. A universally unique identifier (UUID) is a 128-bit number that are unique and large enough as to avoid random collisions, in general.

Unlike natural keys (like social security numbers or passport numbers), surrogate keys have no business meaning. They’re purely technical constructs that provide stable, immutable identifiers immune to real-world changes.

-- Table with surrogate key
CREATE TABLE employee (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,          -- Surrogate key
    social_serurity_number VARCHAR(11) UNIQUE NOT NULL,  -- Natural key
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE,
    department VARCHAR(50)
);

Natural Key

Natural keys derive from the actual data and have business meaning. An ISBN for books, a VIN for vehicles, or a Social Security number for people are all natural keys. While they seem intuitive, natural keys can be problematic when external systems change formatting, when corrections are needed, or when privacy concerns arise. This is where understanding the valid use cases for keys is vital as once we have a key it is very hard to change and usually involves a large migration of data and all the fun that usually entails. An example of a natural key is the ISO-3166 code for countries. 

-- Table using a natural key
CREATE TABLE country (
    country_code CHAR(2) PRIMARY KEY,  -- Natural key (ISO country code)
    country_name VARCHAR(100) NOT NULL,
    population BIGINT,
    capital VARCHAR(100)
);

Unique Key (UK)

Now we venture into keys that may be less familiar. Unique keys enforce uniqueness on non-primary key columns. While a table has only one primary key, it can have multiple unique keys. In an employees table, the primary key might be employee_id, but email and badge_number could both have unique constraints, preventing duplicates without serving as the primary identifier. A unique key is permitted to have null values, unlike a primary key.

-- Employees table with multiple unique keys
CREATE TABLE employee (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,  -- Primary key (surrogate)
    ssn VARCHAR(11) UNIQUE NOT NULL,             -- Unique key #1
    email VARCHAR(100) UNIQUE NOT NULL,          -- Unique key #2
    employee_badge VARCHAR(20) UNIQUE NOT NULL,  -- Unique key #3
    phone VARCHAR(15) UNIQUE,                    -- Unique key #4
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Super Key

These are more subtle and can be conflated with composite keys, but they serve different purposes and follow diffent creation rules. A super key is any combination of attributes that can uniquely identify records. A table can therefore have multiple super keys. At the limit, all the columns in a table rows combined could be classed as a super key, but that would be a monumentally terrible design choice.

-- Simple table
CREATE TABLE student (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT
);

Which of these could be super keys:

Table displaying student records with columns for student_id, email, first_name, last_name, and age.
  • {student_id} = Yes (unique, minimal)
  • {email} = Yes (unique, minimal)
  • {first_name} = No (NOT unique – multiple Alices possible)
  • {last_name} = No (NOT unique – multiple Smiths possible)
  • {age} = No (NOT unique – multiple 20-year-olds)
  • {student_id, email} = Yes (unique, but has extra column)
  • {student_id, age} = Yes (unique, but age is unnecessary)
  • {email, first_name} = Yes (unique, but first_name is unnecessary)
  • {first_name, age} = No (NOT unique – many could match)
  • {ALL COLUMNS} = Yes (always unique!)

Candidate Key

This follows immediately from the definition of super key as a candidate key is a minimal super key, it’s the smallest set of columns that can uniquely identify every row in a table, with no unnecessary attributes. It’s called the candidate as it was the winner of the possible sets of super key combinations that could minimally identify a row, without containing any unecessary columns. The candidate key in the above example would have been the employee_id. Canididate keys cannot be null. In the paper by Codd, he states:

“A candidate key is a combination of attributes having the property that, in any given relation, no two distinct tuples have the same values for these attributes… No proper subset of K has the uniqueness property.”

Business Key

A business key is an attribute (or set of attributes) that uniquely identifies an entity from a business perspective, regardless of how it’s implemented in the database. It’s the real-world identifier that business users understand and use in their daily operations. They are related to natural keys defined earlier, in that it is “natural” because it already exists in the real world rather than being artificially created.

CREATE TABLE library_book (
    -- SURROGATE KEY (primary key)
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- NATURAL KEY: Already exists in the real world
    isbn VARCHAR(13) UNIQUE NOT NULL,  -- Publisher's unique identifier
    
    -- BUSINESS KEY: What the library uses internally
    library_barcode VARCHAR(20) UNIQUE NOT NULL,  -- Library's own tracking number
    
    -- Book details
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    publisher VARCHAR(100),
    publication_year INT,
    
    -- Library-specific data
    acquisition_date DATE,
    shelf_location VARCHAR(20),
    status VARCHAR(20) DEFAULT 'Available'
);

Alternate Key (AK)

The alternate key is closely related to the unique key and can be considered a synonym in most cases. The difference is that alternate keys tend to live in the logical design space and can be both null and minimal and be a candidate key not chosen to be the PK. The unique key on the other hand is usually defined in the physical design space, it can be null and non-minimal.

Hash Key

A hash key is the result of a hash functiuon being applied to one or more attrbiutes. They are used for efficiency, encryption and conformity. Imagine a composite with 5 columns. When checking two records, the database engine must make compare all 5 values.

-- Composite primary key (no hashing)
PRIMARY KEY (country_code, state_code, city_name, street_name, postal_code)

With hashing, this can all take place in a single operation:

PRIMARY KEY (country_code, state_code, city_name, street_name, postal_code)

# Hash the composite key to a fixed-size value:**

Input:  ("US", "CA", "San Francisco", "Market St", "94103")
Output: 8472619384 (fixed 8-byte integer)

In addition, hash algorithms map data of arbitrary size to a fixed-size value. It does not matter how many composite columns we include, the hashing function will always return the same sized output. This helps with efficient storage and improves indexing performance at the same time, as indexes no longer need to handle variable-length multi-column entries. You will see hash keys used when designing a Data Vault 2.0 solution, as described in this post.

Data Vault 2.0

Partition Key (Sharding Key)

A partition key is used to slice the data sets into smaller sets, or partitions. It allows the database engine to allow searches to operate on smaller sets. An example would be to partition by country, ensuring that searches that only relate to the US or UK would operate on these subsets. Partition is related to the concept of sharding, as explained below.

Sharding for Scale

Clustering Key (Sort Key)

A cluster key is a synonym for a partition key. For example, Snowflake uses this approach to manually sort the data in its micro partitions. Care should be taken as these operations are not free, as I once discovered to my alarm when I received my Snowflake invoice.

Snowflake’s Indexing Methods

Intelligent / Smart Key

An intelligent/smart key encodes real meaning in its structure, for example:

CREATE TABLE product (
    product_code VARCHAR(20) PRIMARY KEY,  -- Smart key
    description VARCHAR(200)
);

-- Example smart key: "ELC-TV-55-OLED-2024"

-- ELC = Electronics category
-- TV = Product type
-- 55 = Size (55 inches)
-- OLED = Technology
-- 2024 = Year

They are useful in the sense they are self-documenting and useful for reporting, but they violate normalisation principles, it’s hard to change the encoding scheme and can become outdated as new functional groups become available. To undertand why they break normalisation priciples, please read the following post.

Normal Forms

Spatial Key (Spatial Index)

This special type of index is used to index geographic or spatial data. Essentially, it is used to answer:

  • “all the things inside this space”
  • “all the things near this or other thing”

Creating a sparial index (for example on PostGIS) is really easy:

CREATE INDEX mytable_geom_a ON mytable USING GIST (geom);

External Key

Similar to a foreign key, but designed to denote a relationship to a primary key in another system. It is used to describe a foreign key, especially when importing data from another database or system. In this context, it’s an identifier from an external source that is used to link records in the new database. Unlike a foreign key, it cannot enforce referential integrity as it’s scope is outside the reach of the local database engine.

Identifying vs Non-Identifying Key

In an identifying relationship, the foreign key is part of the childs primary key. An identifying relationship exists when a child record cannot exist without its parent record. The child’s identity is fundamentally tied to the parent. In summary, the foreign key from the parent is part of the child’s primary key. For example, a passport cannot exist without a passport holder. Even if we use a surrogate key for the child table (which is common today), the relationship is still “identifying” if the child logically depends on the parent for its existence.

CREATE TABLE order_item (
    order_id INT,           -- Part of PK (identifying)
    item_number INT,        -- Part of PK
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, item_number),
    FOREIGN KEY (order_id) REFERENCES order(order_id)
);

-- order_id identifies the order_item (can't exist without order)

In a Non-Identifying Relationship, the foreign key is NOT part of child’s primary key. A non-identifying relationship exists when a child record references a parent, but the child can exist independently. An car can exist without an owner. The foreign key is just additional information, not part of the child’s identity.

CREATE TABLE order (
    order_id INT PRIMARY KEY,        -- Independent PK
    customer_id INT,                 -- FK but not part of PK (non-identifying)
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- order_id identifies order independently
-- customer_id just references customer

If a child record can exist meaningfully without the parent then it is non-identifying. Even with surrogate keys again, the logical relationship type (identifying vs non-identifying) still matters for understanding your data model, cascading deletes, and business logic.

Partial Key

A partial key (also called a discriminator) is an attribute used to distinguish instances of weak entities from strong entities – these are two new terms to unpack:

Strong Entity

A strong entity does not depend on any other entity and will always have a primary key and when combined with other strong entities create a strong entity set.

Example: Employee entity with with an Employee ID

Weak Entity

As opposed to a strong entity, a weak entity is dependent on a strong entity for it to exist. Unlike the stroing entity, the weak entity does not need a primary key to exist.

Example: Invoice Line entity as its existence depends on a parent Invoice

The Partial Key is attribute or a set of attributes in a weak entity that helps distinguish instances within a single group related to a strong entity. 

-- Strong Entity: Invoice
CREATE TABLE invoice (
    invoice_id INT PRIMARY KEY, -- Strong key (independent)
    customer_id INT NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

-- Weak Entity: Invoice line
CREATE TABLE invoice_line_item (
    invoice_id INT,                    -- Borrowed from parent (discriminator)
    line_number INT,                   -- Partial key (weak key)
    product_id INT NOT NULL,
    description VARCHAR(200),
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    line_total DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (invoice_id, line_number),  -- Composite: borrowed + partial
    FOREIGN KEY (invoice_id) REFERENCES invoice(invoice_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- line_number is the partial key (weak key)
-- line_number is only unique within a specific invoice
-- line_number alone cannot identify a record globally
-- Needs `invoice_id` (from parent) to form complete identity

Related Posts

Trending now

Log-Structured Merge Trees
FinOps Maturity Curve for Data
Normal Forms
Anchor Modeling

Discover more from Data Lingua. Where Data Engineering Meets Agentic Business Strategy

Subscribe now to keep reading and get access to the full archive.

Continue reading