Keys in DBMS

Introduction

A key in a database management system is an attribute, or a group of attributes, used to identify rows, enforce uniqueness, and connect related tables. Keys give records reliable identities and allow a relational database to distinguish one real-world entity from another even when descriptive values are identical.

Consider two customers named John who live in Chicago. Name and city do not tell the database which John placed an order, changed an address, or requested account deletion. A customer_id such as 101 or 102 removes the ambiguity. The value becomes the stable reference used by orders, payments, addresses, and other related records.

Database keys are not all the same. Super keys and candidate keys describe possible identifiers. A primary key is the chosen main identifier. Alternate and unique keys preserve other valid uniqueness rules. Foreign keys create relationships. Composite, natural, surrogate, simple, and partial keys describe how an identifier is formed or where its meaning comes from. Understanding these distinctions is fundamental to relational design.

Why Keys Are Important

Keys let a database identify a precise row for reading, updating, and deleting. They prevent invalid duplicates, preserve business uniqueness, and define how one table references another. Without reliable keys, an update may affect several rows unintentionally, duplicate entities may accumulate, and relationships become dependent on vague text comparisons.

Keys also support normalization and functional dependencies. They help designers state which attributes determine the rest of a row, separate entities correctly, and choose the grain of transaction and junction tables. SQL engines frequently support key constraints with indexes, which can also make lookups and joins efficient.

Reliable key
    |
    +-- identifies one row
    +-- enforces uniqueness
    +-- supports relationships
    +-- protects integrity
    +-- guides normalization

A Table Without a Reliable Key

The two John rows may represent different people or an accidental duplicate. A statement based on name and city cannot safely target one of them. Adding a unique customer_id gives every row a distinct identity while allowing descriptive values to repeat legitimately.

Main Types of Database Keys

Super Keys

A super key is any set of one or more attributes whose values uniquely identify a row. Suppose an Employee table guarantees that employee_id and email are individually unique. The sets {employee_id} and {email} are super keys. Sets such as {employee_id, name} and {email, name} are also super keys because they still contain a unique identifier.

A super key may include unnecessary attributes. If employee_id alone identifies the row, adding name does not improve uniqueness. This broad definition is useful because candidate keys are found by reducing super keys until no attribute can be removed without losing uniqueness.

Candidate Keys

A candidate key is a minimal super key. It uniquely identifies every row, and none of its attributes is redundant for that purpose. If employee_id and email are each guaranteed unique, both are candidate keys. The combination of employee_id and name is not a candidate key because employee_id already works alone.

A table can have several candidate keys. A Customer table might have customer_id, email, and national_customer_number as candidate keys if the business guarantees all three are unique, non-null identifiers. The designer normally chooses one as the primary key and protects the others as alternate business keys.

Super keys
    |
    +-- remove unnecessary attributes
            |
            v
       Candidate keys
            |
            +-- choose one primary key
            +-- preserve remaining alternate keys

Primary Keys

A primary key is the candidate key selected as the table's main row identifier. It must be unique and cannot be NULL. A table has one primary key constraint, although that constraint may contain one column or several columns.

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

Here, customer_id is the primary key. The database rejects duplicate customer_id values and rows without one. Email remains unique but is not the main internal identity. Good primary keys are usually stable, minimal, efficient to reference, and unlikely to be reused.

Primary Key Properties

Uniqueness ensures that one key value maps to at most one row. NOT NULL ensures that every entity row has an identity. Minimality avoids carrying columns that add storage and complexity without contributing to identification. Stability reduces the need to update references across related tables.

These are not merely stylistic preferences. A primary key can be referenced by many foreign keys and included in many indexes. A wide or frequently changing primary key increases storage, join cost, update work, and operational risk. The best choice represents identity reliably throughout the row's lifetime.

Alternate Keys

Candidate keys not selected as the primary key are alternate keys. If user_id, username, and email can each identify a user, choosing user_id as primary makes username and email alternate keys. Their business uniqueness should still be enforced.

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    display_name VARCHAR(150) NOT NULL
);

Alternate keys are not secondary in importance. They prevent duplicate business identities and support lookup and integration workflows. The word alternate describes their relationship to the chosen primary key, not whether their uniqueness is optional.

UNIQUE Constraints

An SQL UNIQUE constraint enforces uniqueness on one column or a combination of columns. A table can have multiple UNIQUE constraints but only one primary key constraint. Candidate keys are relational-model concepts; PRIMARY KEY and UNIQUE are common SQL mechanisms used to implement them.

NULL behavior for UNIQUE constraints differs among database products. Some allow multiple NULL values because NULL is not equal to another NULL, while others offer different semantics or filtered-index options. Do not assume that UNIQUE(email) also means every row must have an email. Add NOT NULL when the business rule requires a value.

Primary Key Versus UNIQUE

Foreign Keys

A foreign key is a column or group of columns whose values must match an eligible referenced key in a parent table, unless a permitted NULL represents an absent optional relationship. Foreign keys connect relations and enforce referential integrity.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The database rejects an order that references a customer that does not exist. A foreign key does not generally need to be unique. In a one-to-many relationship, several orders can contain customer_id 101 because all belong to the same customer.

Optional and Mandatory Foreign Keys

Nullability expresses minimum cardinality. If every order must belong to a customer, customer_id should be NOT NULL in addition to being a foreign key. The reference constraint proves that a non-null value exists in Customers; NOT NULL proves that the relationship is mandatory for every Order.

If an employee may have no manager, manager_id can allow NULL. A NULL value means that no manager relationship is currently recorded. Optionality should be intentional and documented, not an accidental result of omitting NOT NULL.

Self-Referencing Foreign Keys

A foreign key can reference the same table. An Employees table can use manager_id to reference employee_id, creating a recursive one-to-many hierarchy. One manager may appear in many employee rows, while each employee has at most one direct manager.

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id BIGINT NULL,
    FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
);

Additional constraints or application logic may prevent an employee from managing themselves and detect cycles when the domain requires a strict tree.

Composite Keys

A composite key uses multiple columns together. In an Enrollment table, student_id is not unique because one student can take many courses. Course_id is not unique because one course can have many students. The pair can uniquely identify one enrollment when repeated enrollment in the same course is not allowed.

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

A primary key describes the key's role, while composite describes its number of columns. A primary key can therefore be simple or composite.

Composite Foreign Keys

A foreign key can also contain multiple columns. If a Department is identified by company_id and department_code, a child reference must carry the same complete pair. Referencing only department_code would be ambiguous when different companies reuse the same code.

FOREIGN KEY (company_id, department_code)
REFERENCES departments (company_id, department_code)

Column order, data types, and semantic roles must align with the referenced key. Composite foreign keys can widen child tables and indexes, which is one reason some designs use a surrogate parent identifier while retaining a UNIQUE constraint on the natural business combination.

Simple and Composite Keys

A simple key contains one attribute, such as customer_id. A composite key contains more than one, such as (order_id, line_number). Neither category is automatically better. The correct structure depends on what uniquely identifies the row and how that identity will be referenced.

Composite keys communicate business grain directly but make every referencing foreign key wider. Surrogate simple keys make references compact but require separate constraints to protect natural uniqueness. Design should consider both logical correctness and operational cost.

Natural Keys

A natural key comes from business data. A vehicle identification number, ISBN, employee number, product SKU, or regulated identifier can be a natural key when the domain guarantees its uniqueness and validity. Natural keys carry meaning and may already be used by external systems.

The risk is that apparent uniqueness may not be permanent. Business formats change, identifiers are corrected or reused, and sensitive values may be unsuitable for broad exposure. Long natural keys enlarge indexes and every foreign key that copies them. Before choosing one as primary, verify stability, scope, privacy, and lifecycle rules.

Surrogate Keys

A surrogate key is an artificial identifier created specifically for database identity. Common forms include sequence-generated integers, identity columns, and UUIDs. It usually has no business meaning, which can be beneficial because its value remains independent of changing business attributes.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL
);

Product_id provides stable internal identity, while SKU preserves the business uniqueness rule. A surrogate key does not eliminate candidate keys or business constraints. Without UNIQUE on SKU, the table could store duplicate products under different product_id values.

Natural Versus Surrogate Keys

Business Keys Alongside Surrogate Keys

A common production pattern uses both an internal surrogate primary key and a unique business key. An Order table may use order_id for relationships and order_number for communication with customers, support teams, and integrations.

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

This pattern separates technical identity from business presentation. Order numbers can follow a meaningful format without being copied into every child table, while the UNIQUE constraint ensures that the public number still identifies one order.

Partial Keys and Weak Entities

In entity-relationship modeling, a partial key identifies a weak entity only within its owning entity. An order item's line_number may be unique inside one order but repeat across different orders. Line number 1 is not globally unique.

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Line_number is the partial key, and order_id supplies the owner's identity. Together they form the complete primary key for an order item.

Keys and Entity Integrity

Entity integrity means that each row representing an entity can be uniquely identified. A primary key supports this rule through uniqueness and non-null identity. Two entity rows cannot share the same primary key, and no entity row can lack one.

This is why a table containing important independent entities normally needs a declared primary key or an equivalent candidate key chosen as its identifier. Merely hoping that current data happens to be unique does not establish integrity.

Keys and Referential Integrity

Referential integrity means that relationships point to valid records. If Orders.customer_id references Customers.customer_id, every non-null child value must match an eligible parent key. A database without this constraint can accumulate orphan orders referring to customers that never existed or were deleted incorrectly.

Foreign-key actions such as RESTRICT, CASCADE, SET NULL, or product-specific equivalents define what happens when a referenced key is updated or deleted. Choose them from lifecycle and retention requirements rather than applying cascade automatically.

Keys in Relationship Patterns

One-to-one relationships can use a shared primary key. A UserProfile.user_id can be both its primary key and a foreign key to Users, allowing at most one profile per user. One-to-many relationships place a repeatable foreign key on the many side, such as Orders.customer_id.

Many-to-many relationships introduce a junction table containing foreign keys to both entities. UserRole may use (user_id, role_id) as a composite primary key, preventing the same role from being assigned twice to one user. Each relationship pattern depends on the correct key constraint to express cardinality.

1:1  USER -- USER_PROFILE(user_id PK/FK)

1:N  CUSTOMER -- ORDER(customer_id FK)

N:M  STUDENT -- ENROLLMENT(student_id, course_id) -- COURSE

Choosing a Primary Key

Start by identifying every attribute set that the business guarantees to be unique. Reduce each set to a candidate key, then compare stability, nullability, size, privacy, generation strategy, and use across relationships. The chosen primary key should identify the entity rather than a temporary description of it.

Names, phone numbers, and email addresses are frequently poor primary keys because they can duplicate, change, be reformatted, be reassigned, or be absent. A surrogate key plus a UNIQUE constraint on the current business identifier is often safer. However, a stable compact natural key can still be appropriate when its guarantee is strong.

Stable and Mutable Keys

Changing a primary key can require coordinated updates to orders, payments, addresses, subscriptions, and every other referencing table. Cascading updates may be supported, but broad identity changes increase locking, logging, replication, and operational risk.

A key with no business meaning is not inherently bad. Its responsibility is reliable identity. Business meaning can remain in separately constrained attributes that are free to change without redefining the row or its relationships.

Key Data Types

Key data types affect storage, index width, cache use, network payloads, and join operations. A primary-key value is often repeated in several foreign keys and indexes, so even a modest size difference can multiply across a large database.

Use types that cover the expected domain without unnecessary width. Keep referenced and referencing column types compatible. Avoid embedding formatting into identifiers unless it is truly part of the business key. The correct choice depends on scale, generation, distribution, migration, and interoperability requirements.

Integer Versus UUID Surrogate Keys

Integer keys are compact, efficient to compare, and easy to generate from a sequence or identity mechanism. Sequential values generally provide index locality, although their predictability may be unsuitable for public identifiers when enumeration is a security or privacy concern.

UUIDs can be generated across distributed services without one central sequence and have an extremely low collision probability when generated correctly. They require more storage than typical integer keys and can affect index locality depending on version and storage order. There is no universal winner; architecture and workload determine the tradeoff.

Keys Are Not the Same as Indexes

A key is a logical concept describing identity, uniqueness, or a relationship. An index is a physical access structure used to find and validate data efficiently. Database systems often create indexes to enforce primary and UNIQUE constraints, but the constraint and index are not interchangeable concepts.

An index can exist without declaring a business key, and a foreign key does not universally cause an automatic child-column index. Keep logical correctness and physical optimization separate: define constraints because the data rule is true, then design supporting indexes for the workload.

Primary, Unique, and Foreign-Key Indexing

Most relational engines enforce a primary key and UNIQUE constraints through unique indexes or equivalent structures. Those structures detect duplicates efficiently and often support direct lookups. Exact clustering and storage behavior depends on the database product and configuration.

Foreign-key columns commonly participate in joins, parent-child filters, and referential checks. An index such as CREATE INDEX idx_orders_customer ON orders(customer_id) can improve these operations, but it adds storage and write cost. Use execution plans and workload evidence rather than indexing every column automatically.

Keys and JOINs

Joins follow relationships expressed by compatible keys. A Customer primary key joins to the corresponding foreign key in Orders, producing reliable matches without depending on mutable names or emails.

SELECT
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE c.customer_id = 101;

Declared constraints also help humans and tools understand the schema. Query builders, documentation systems, migration tools, and database optimizers can use relationship metadata more effectively than informal naming conventions alone.

Keys, Functional Dependencies, and Normalization

A functional dependency states that one attribute set determines another. If employee_id identifies an employee, then employee_id determines employee_name, hire_date, and department_id. A candidate key functionally determines every attribute in its relation.

employee_id
    -> employee_name, hire_date, department_id

(student_id, course_id)
    -> enrollment_date, grade

Normalization relies on candidate keys and dependencies to detect repeated facts and update anomalies. In Enrollment, grade depends on the student-course association, not on Student alone or Course alone. Understanding the key reveals where the attribute belongs.

Complete E-Commerce Key Design

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(150) NOT NULL
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Customer_id, product_id, and order_id are surrogate primary keys. Email, SKU, and order_number are alternate business keys enforced with UNIQUE. Orders.customer_id and OrderItems.product_id are foreign keys. The composite key on order_id and line_number identifies each dependent order item. Each constraint has a distinct role.

Common Key Design Mistakes

A table without a reliable key makes updates, deletes, deduplication, and relationships unsafe. Choosing a non-unique name as the primary key is equally problematic. Always validate uniqueness from business guarantees, not from a small sample of current data.

Another mistake is using a surrogate primary key but omitting business uniqueness. Different user_id values do not prevent duplicate email accounts if email must be unique. Conversely, adding UNIQUE to Orders.customer_id accidentally limits each customer to one order and changes a one-to-many relationship into a maximum one-to-one relationship.

Very wide mutable composite keys increase index and foreign-key size. A pair used for a junction table may also be insufficient when the same relationship can recur over time. Employee and project IDs do not uniquely identify several assignment periods unless another discriminator is included.

Finally, confusing a key with an index leads teams to remove constraints after adding performance indexes. An ordinary index does not necessarily enforce uniqueness or referential integrity. Keep the data rule declared explicitly.

Key Design Workflow

Identify the entity and row grain
        |
List business-guaranteed unique attributes
        |
Find super keys and reduce to candidate keys
        |
Choose a stable primary key
        |
Declare alternate UNIQUE constraints
        |
Create foreign keys for relationships
        |
Validate nullability and cardinality
        |
Choose simple, composite, natural, or surrogate form
        |
Add workload-driven supporting indexes
        |
Test duplicates, invalid references, and lifecycle changes

Ask whether the proposed key can change, be reused, be NULL, expose sensitive information, or become too large when copied into child tables. Also ask whether historical duplicates can exist and whether a key identifies the true entity or merely one version of it.

Interview Questions and Answers

What is a key in DBMS? It is one attribute or a set of attributes used to identify rows, enforce uniqueness, or establish relationships.

What is the difference between a super key and candidate key? A super key uniquely identifies a row but may contain extra attributes. A candidate key is a minimal super key.

What is the difference between a candidate and primary key? Every primary key is a selected candidate key. Other candidate keys become alternate keys.

Can a primary key be composite? Yes. Primary describes its role; composite means it contains multiple columns.

Can a foreign key repeat or be NULL? It can repeat in relationships such as one-to-many. It can be NULL when the relationship is optional and the column permits NULL.

Does a surrogate key replace business uniqueness? No. Business candidate keys still need PRIMARY KEY or UNIQUE constraints.

Are keys and indexes the same? No. Keys define logical identity and integrity. Indexes are physical structures commonly used to enforce or access them efficiently.

Practical Review Checklist

• Define the exact grain represented by one table row.
• Identify candidate keys from guaranteed business rules.
• Choose a unique, non-null, stable, and minimal primary key.
• Preserve alternate business keys with UNIQUE constraints.
• Use foreign keys to enforce valid relationships.
• Match nullability and uniqueness to relationship cardinality.
• Use composite keys only when the complete combination defines identity.
• Keep surrogate identity separate from business uniqueness.
• Select data types with storage, indexing, and distribution in mind.
• Add supporting indexes from actual query and maintenance needs.

Key Takeaway

Keys are the foundation of row identity, uniqueness, and relationships in a relational database. Super keys are any unique attribute sets; candidate keys are minimal super keys; the primary key is the selected candidate; and alternate keys are the remaining candidates. Foreign keys connect rows while preserving referential integrity.

SUPER KEY
    |
    v
CANDIDATE KEY (minimal)
    |
    +-- PRIMARY KEY (selected)
    +-- ALTERNATE KEYS (remaining)

FOREIGN KEYS connect related tables.

Composite, natural, surrogate, simple, and partial describe how identity is formed or interpreted. The best design combines stable internal identity with explicit business constraints, chooses cardinality carefully, and keeps logical keys distinct from performance indexes. When keys accurately represent business meaning, the rest of the relational model becomes safer and easier to query.