Alternate Key in SQL and DBMS

Introduction

An alternate key is a candidate key that was not selected as the table's primary key. A relation can have several minimal ways to identify one row. Database design chooses one of those candidates for the primary role, while every remaining candidate becomes an alternate key. The role changes, but the alternate key remains unique, minimal, and capable of identifying every valid row.

Consider an employee table in which employee_id, employee_number, and email are each mandatory and guaranteed unique. All three are candidate keys. If employee_id is selected as the primary key, employee_number and email are alternate keys. They still protect meaningful business identities and should normally remain enforced.

SQL usually has no standard ALTERNATE KEY keyword. The concept is implemented through a UNIQUE constraint, normally combined with NOT NULL. Understanding the difference between the conceptual key and its SQL mechanism prevents teams from labeling every unique column as an alternate key.

Basic Definition

Candidate keys are all minimal attribute sets that can uniquely identify a row. Exactly one candidate is selected as the primary key. The other candidates are alternatives that could have served as primary, so they are called alternate keys.

Candidate keys:
  employee_id
  employee_number
  email

Selected primary key:
  employee_id

Alternate keys:
  employee_number
  email

The relationship can be remembered as: alternate keys equal candidate keys minus the selected primary key.

Why It Is Called Alternate

The word alternate does not mean backup data, optional data, or a secondary index. It means the key was a valid alternative during primary-key selection. If a customer can be identified by customer_id, customer_number, or email, any one could theoretically be selected. After customer_id wins the primary role, the other two remain alternate identifiers.

This definition depends on first finding all candidate keys. A designer cannot correctly identify alternate keys by scanning the columns after a primary key has already been chosen.

Candidate Key Versus Alternate Key

Candidate key describes eligibility. Alternate key describes the role of an eligible key after another candidate is selected. Before selection, every minimal unique identifier is simply a candidate. After selection, one is primary and the rest are alternate.

An Alternate Key Must Be Unique

Because an alternate key remains a candidate key, no two rows can share its complete value. If email is an alternate key for customers, the database must reject a second customer with the same normalized email under the business comparison rules.

Uniqueness is not only a convenient validation. It is part of the identity claim. If duplicates are legitimate, the attribute does not identify one row and therefore cannot be an alternate key by itself.

An Alternate Key Must Be Minimal

Minimality means no attribute can be removed while uniqueness remains. If email alone identifies a user, the pair (email, display_name) is unique but not minimal. It is a superkey rather than a candidate or alternate key because display_name contributes nothing to identification.

For a composite alternate key, remove each member conceptually and test the resulting subset. Every included column must be necessary under valid business data.

SQL Has No Standard ALTERNATE KEY Constraint

Relational modeling names the concept, while SQL supplies mechanisms to enforce it. A primary candidate uses PRIMARY KEY. An alternate candidate usually uses UNIQUE together with NOT NULL.

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    employee_number VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    employee_name VARCHAR(100) NOT NULL
);

In this design, employee_number and email are conceptually alternate keys only if business rules make them complete minimal identifiers.

Alternate Key Versus UNIQUE Constraint

An alternate key is a data-modeling concept. UNIQUE is a database constraint. UNIQUE is the usual enforcement mechanism, but the terms are not synonyms. A column may be constrained as unique for an operational reason without qualifying as an identity for every row.

For example, locker_number might be unique whenever assigned but NULL for employees without lockers. It prevents duplicate assignments, yet it does not identify every employee and is not a candidate or alternate key.

Not Every Unique Column Is an Alternate Key

A valid alternate key must be unique, minimal, complete, and meaningful as an identifier under the data model. Accidental uniqueness in current records is not enough. Temporary codes, optional external references, and nullable identifiers may need UNIQUE constraints but fail candidate-key requirements.

Business semantics determine key status. SQL syntax can enforce a rule, but it cannot decide whether that rule represents entity identity.

Primary Key Versus Alternate Key

Primary does not mean more unique. Both roles identify one row. The primary key is simply the candidate chosen for the table's principal referencing role.

Multiple Alternate Keys

A table can have many alternate keys. A user table may guarantee uniqueness for user_id, username, email, and an external user number. If user_id is primary, the other three are alternate candidates and require independent constraints.

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    external_user_number VARCHAR(50) NOT NULL UNIQUE
);

Each constraint protects a separate identity. A failure in one does not imply that the others should be removed.

Natural Alternate Keys

Alternate keys are often natural or business identifiers. A product SKU, barcode, employee number, customer number, vehicle identification number, or order number may already exist in the domain. If it is mandatory, guaranteed unique, and minimal, it can be an alternate key when a different candidate becomes primary.

Natural values make business lookups and integration easier, but stability, privacy, formatting, and reuse still need evaluation.

Surrogate Primary Key With Natural Alternate Keys

A common design selects a generated surrogate such as product_id as primary while preserving SKU and barcode as alternate keys. The surrogate offers a compact, stable internal reference. The natural alternates preserve the uniqueness the business actually recognizes.

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

Adding a surrogate does not replace natural uniqueness. Without the alternate constraints, multiple rows could represent the same real product.

Customer Example

A customer relation may contain customer_id, customer_number, email, and customer_name. If the first three are each guaranteed identifiers, they are candidate keys. Selecting customer_id as primary makes customer_number and email alternate keys.

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

Employee Example

An employee may have an internal employee_id, a company-issued employee_number, and a corporate email. Employee_id is often selected as primary because it is compact and stable. Employee_number and email can remain alternate keys if they are mandatory, unique for the intended scope, and never reassigned in a conflicting way.

If numbers are only unique within a company, the true candidate is (company_id, employee_number), not employee_number alone.

Order Example

Orders commonly use an internal numeric ID and a business-facing order number. The internal value serves joins efficiently, while support teams and customers use the order number.

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

Order_number is an alternate key if it uniquely and permanently identifies one order throughout the required history.

Vehicle Example

A vehicle_id and VIN may both be candidates, while registration number may not be. Registration can change, differ by jurisdiction, or be reused. This example shows why apparent business significance does not automatically establish candidate-key status. The rule must cover the complete lifecycle and defined population.

An Alternate Key Can Be Composite

Alternate describes a role, not the number of columns. An employee assignment can have a surrogate assignment_id as primary while the combination of employee_id, project_id, and start_date forms a composite alternate key.

CREATE TABLE employee_assignments (
    assignment_id BIGINT PRIMARY KEY,
    employee_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    start_date DATE NOT NULL,
    CONSTRAINT uq_employee_assignment
        UNIQUE (employee_id, project_id, start_date)
);

The combination prevents duplicate representations of the same assignment event. Each component may repeat individually while the complete tuple remains unique.

Tenant-Scoped Alternate Keys

In multi-tenant systems, a username, invoice number, or employee number may be unique only within one tenant. The tenant identifier belongs in the candidate. A global UNIQUE constraint on the local number would incorrectly prevent different tenants from using the same value.

CONSTRAINT uq_tenant_invoice
    UNIQUE (tenant_id, invoice_number)

If invoice_id is the primary key, the tenant-number pair is a composite alternate key. API lookups and import jobs must include the same scope.

Business Rules Define Alternate Keys

Ask whether two customers can share an email, whether email is mandatory, whether comparison ignores case, whether an address can be reassigned after deletion, and whether historical records stay in the table. Each answer can change whether email qualifies as a candidate.

A data sample can demonstrate violations but cannot prove a universal business guarantee. Key design comes from domain rules supported by data profiling.

Current Uniqueness Is Not Enough

If every current customer happens to live in a different city, city is still not an alternate key. Future customers can share cities, and the domain never promised uniqueness. Declaring UNIQUE from a coincidental sample would reject valid data.

Use historical analysis to challenge assumptions, but obtain an explicit rule from the domain before modeling identity.

Stability and Mutability

An alternate key can be mutable. Email may remain unique while changing from one valid value to another. That mutability often makes it less attractive as the primary key or as a widely propagated foreign key. A stable surrogate can carry internal references while the mutable alternate remains constrained for lookup and validation.

Changes should occur transactionally, preserve audit history when required, and reject a new value already owned by another row.

Why Alternate Keys Must Be Preserved

Choosing one primary key does not invalidate the other candidate rules. If the database omits UNIQUE(email), two rows can represent the same email identity despite having different primary IDs. Application-only checks can race under concurrent requests.

Database constraints provide the final authority. Services can validate earlier for better messages, but they should interpret constraint conflicts rather than replacing them.

Business Identity and Technical Identity

A surrogate primary key answers which internal row this is. A customer number or order number answers which entity business users and external systems recognize. Keeping both roles explicit improves support, reporting, integration, and data quality.

Public identifiers should be evaluated for privacy and predictability. An alternate key can support a public lookup without becoming the internal foreign-key target everywhere.

Foreign Keys Can Reference Alternate Keys

Relational foreign keys can reference a candidate key, including an alternate key, when the target has an eligible unique constraint and the DBMS requirements are met. For example, an office can reference a unique country code rather than country_id.

CREATE TABLE countries (
    country_id INT PRIMARY KEY,
    country_code CHAR(2) NOT NULL UNIQUE
);

CREATE TABLE offices (
    office_id INT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    FOREIGN KEY (country_code)
        REFERENCES countries(country_code)
);

Should Foreign Keys Reference Alternate Keys?

They can, but the choice should consider stability, width, privacy, collation, update frequency, and integration needs. A short stable country code may be reasonable. A long mutable email copied into many child tables creates expensive updates and exposure.

Using the primary surrogate for internal relationships while retaining alternate keys for validation and lookup is often simpler. This is a design judgment, not a universal prohibition.

Alternate Keys and Normalization

Normalization must consider every candidate key, not only the primary key. Functional dependencies involving alternate candidates affect normal forms, determinants, decomposition, and anomaly analysis. In a user relation where user_id, username, and email are all candidates, each can determine the remaining non-key attributes.

BCNF analysis is particularly concerned with whether every determinant is a superkey. Ignoring alternate candidates can produce an incomplete or incorrect dependency model.

Prime Attributes

A prime attribute belongs to at least one candidate key. If user_id is primary and email is alternate, both are prime attributes because both participate in candidate keys. Prime status is not limited to columns in the selected primary key.

This distinction matters in formal normalization definitions and interview questions about partial and transitive dependencies.

Alternate Key Versus Superkey

Every alternate key is a superkey because it identifies rows, but not every superkey is an alternate key. If email alone is an alternate key, (email, name) may still uniquely identify a row. It is a non-minimal superkey because name is unnecessary.

Alternate Key Versus Foreign Key

An alternate key uniquely identifies rows in its own relation. A foreign key references an eligible key in another or related relation. A column can participate in both roles in unusual models, but the terms describe completely different constraints and purposes.

Alternate, Composite, Natural, and Business Keys

Alternate describes selection role. Composite describes structure. Natural and business describe origin or domain meaning. One key can therefore be a composite natural alternate key, such as (company_id, employee_number) when employee_id is the primary key.

Keeping these dimensions separate prevents debates in which different terms are treated as mutually exclusive even though they answer different questions.

Alternate Keys and NULL

A candidate key must identify every row, so its attributes should be complete. SQL UNIQUE constraints can permit one or multiple NULL values depending on the database product and constraint semantics. A nullable unique column may therefore enforce partial uniqueness without implementing a complete candidate key.

Use NOT NULL UNIQUE for a single-column alternate candidate. For a composite candidate, make every component NOT NULL unless the formal domain model provides a different identity rule implemented explicitly.

Comparison and Normalization Rules

Textual alternate keys need defined comparison semantics. Are emails case-insensitive? Are leading spaces removed? Are Unicode variants equivalent? Does the collation treat accented characters as equal? The database constraint and application normalization must agree.

A generated normalized column or database-specific expression index can enforce the canonical form. Store the original display value separately if preserving user input matters.

Deletion, Soft Deletion, and Reuse

If a deleted customer's email can be reused, email may not remain unique across retained history. Soft-deleted rows in the same table make the conflict visible immediately. A filtered unique index may enforce uniqueness only among active rows, but that rule describes current active identity rather than a timeless candidate across all stored rows.

Document the scope precisely. Audit, legal, and integration requirements may favor retaining a never-reused alternate number even when mutable contact data can be reused.

Historical and Temporal Alternate Keys

An employee number reused after departure is not globally unique across employment history. If uniqueness is scoped by company or employment period, the complete candidate needs the relevant discriminator. A design might use (company_id, employee_number), or model separate employment episodes with effective dates.

Temporal uniqueness can be more complex than an ordinary UNIQUE constraint because periods must not overlap. Use database capabilities or transactional validation suited to the DBMS rather than pretending a partial identifier is globally unique.

Alternate-Key Selection Is Deliberate

When employee_id, employee_number, and email are candidates, employee_id may be selected as primary because it is stable, compact, generated internally, non-sensitive, and easy to reference. That decision leaves the others as alternates; it does not reduce their validity or importance.

Primary selection should consider stability, width, generation, privacy, business change, and reference frequency. It should not be based solely on which column appears first.

Indexing and Lookup Performance

Databases commonly support UNIQUE constraints with unique indexes or equivalent structures. As a result, alternate-key lookups such as WHERE employee_number = 'E1001' can be efficient and return at most one row. Constraint and index are related but conceptually distinct.

Do not automatically add a second identical index. Inspect the DBMS-generated index, column ordering, included columns, and query plans. Wide alternate keys increase index storage and write cost, particularly when referenced elsewhere.

Alternate Keys in Integrations and APIs

External systems often know a record by customer_number, order_number, SKU, or another alternate key while the service stores an internal primary ID. Import pipelines can resolve the alternate key to the internal row, and APIs can expose business-oriented routes such as /customers/by-number/CUST-1001.

Return a conflict response when a requested alternate value is already assigned. Do not expose sensitive alternate keys merely because they are unique, and do not confuse hard-to-guess values with authorization.

Alternate Keys in Events, Imports, and Reconciliation

Messages and import files often cannot carry the receiving database's internal primary key. They instead contain an order number, supplier code, employee number, or another business identifier. A properly constrained alternate key lets the receiver resolve that external value to exactly one local row. Without the constraint, duplicate local matches make integration behavior ambiguous and can cause updates to the wrong entity.

Idempotent processing commonly depends on an alternate identity. A payment event may have a generated local payment_id as primary while (provider_id, provider_transaction_id) is an alternate key. If the provider retries the same event, the database can recognize the existing transaction instead of creating another payment. The provider scope is essential because two providers may issue the same transaction text.

Batch imports should distinguish a duplicate of the same entity from a conflict between different entities. Resolve values using normalized alternate keys, report rows that map to more than one legacy record, and avoid silently selecting the first match. When source systems have overlapping number ranges, include source_system in the composite alternate key until a governed enterprise identifier exists.

Reconciliation reports benefit from storing both internal and alternate identifiers. The primary key supports reliable joins inside the platform, while the alternate value lets operations teams compare database records with invoices, files, event logs, and partner reports. If an alternate value changes, retain effective-dated mapping or audit history when historical messages must remain traceable.

Contracts should document ownership, case rules, formatting, scope, mutability, and reuse. Otherwise one system may treat an identifier as permanent while another recycles it. Database constraints protect the local rule, but cross-system governance ensures every participant is enforcing the same identity.

Adding an Alternate Constraint to Existing Data

Before adding UNIQUE and NOT NULL to a populated table, profile duplicate values, NULLs, formatting differences, and normalization collisions. Resolve conflicts using business evidence instead of deleting arbitrary rows.

SELECT normalized_email, COUNT(*) AS row_count
FROM users
GROUP BY normalized_email
HAVING COUNT(*) > 1;

For large systems, use a staged migration: populate normalized values, validate data, create the constraint with a low-impact DBMS strategy, deploy compatible code, and then make the new rule mandatory.

Changing an Alternate Key Safely

If the business changes identifier rules, update schema, application validation, API contracts, data pipelines, and references together. When a mutable alternate is referenced by foreign keys, cascading updates may be possible but can create broad write activity.

Keep stable internal identity during the transition, record mappings from old to new business values where needed, and test rollback and concurrent writes. A uniqueness rule is a cross-system contract, not just one DDL statement.

Testing Alternate Keys

Test a valid insert, an exact duplicate, NULL, normalized duplicates, case variants, updates to unused values, and updates to occupied values. For composite alternates, verify that each component can repeat individually where valid but the complete tuple cannot repeat.

Run concurrent inserts with the same value to prove that the database resolves races. Include soft deletion, reuse, tenant scope, historical rows, imports, and API conflict handling. Tests should reflect the declared lifecycle, not only the happy path.

Common Design Mistakes

Common errors call every UNIQUE column an alternate key, confuse alternate and foreign keys, choose alternates before identifying candidates, or claim a table has multiple primary keys. Other mistakes omit natural uniqueness after adding a surrogate, overlook composite alternates, allow NULL in candidate attributes, and reference mutable alternates throughout the schema.

The remedy is a disciplined sequence: define row grain, identify guaranteed minimal identifiers, choose one primary, enforce the remaining alternates, and test their complete lifecycle.

Alternate-Key Design Workflow

Define the relation and row grain
    -> identify all guaranteed unique sets
    -> remove non-minimal superkeys
    -> confirm completeness and lifecycle scope
    -> select the best primary candidate
    -> classify remaining candidates as alternate
    -> enforce UNIQUE and NOT NULL
    -> validate references, migration, and concurrency

Review assumptions whenever business policies change. A key can lose candidate status if sharing, reuse, optionality, or scope changes.

Key Hierarchy

Complete Product Example

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    sku VARCHAR(50) NOT NULL,
    barcode VARCHAR(50) NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    CONSTRAINT uq_product_tenant_sku
        UNIQUE (tenant_id, sku),
    CONSTRAINT uq_product_barcode
        UNIQUE (barcode)
);

Product_id is the primary candidate. The tenant-SKU pair and barcode are alternate candidates if the domain guarantees their uniqueness and completeness at those scopes. SKU alone is not a key when different tenants can reuse it.

Interview Questions and Answers

What is an alternate key? A candidate key that was not selected as the primary key.

Can a table have multiple alternate keys? Yes. Every unselected candidate is an alternate key.

How is an alternate key implemented in SQL? Usually with UNIQUE plus NOT NULL, because SQL normally has no standard ALTERNATE KEY keyword.

Is every UNIQUE column an alternate key? No. It must also be minimal, complete, and valid as a row identifier under business rules.

Can an alternate key be composite? Yes. Alternate describes role, while composite describes structure.

Can a foreign key reference an alternate key? Yes, when the target has an eligible unique constraint and the DBMS supports the reference.

Can an alternate key contain NULL? Conceptually no, because it must identify every row. Use NOT NULL when implementing a candidate.

What happens to other candidate keys after primary selection? They remain alternate keys and should usually remain enforced.

Practical Review Checklist

• Define what one row represents before identifying keys.
• Find all candidate keys before choosing the primary key.
• Confirm every alternate is unique and minimal.
• Require complete non-null values for candidate identity.
• Distinguish an alternate key from a generic UNIQUE rule.
• Include tenant, company, period, or other scope when required.
• Define case, collation, trimming, and normalization semantics.
• Evaluate mutation, deletion, reuse, and historical records.
• Keep natural uniqueness after introducing a surrogate primary key.
• Choose foreign-key targets based on stability and ownership.
• Profile existing data before adding constraints.
• Test duplicates, NULL, concurrency, updates, and composite subsets.

Key Takeaway

An alternate key is not merely another unique field. It is a complete, minimal candidate identifier that remains after one candidate is chosen as primary. Its business identity remains valid and should generally be protected even though it is not the table's principal key.

ALTERNATE KEYS
    = ALL CANDIDATE KEYS
    - THE SELECTED PRIMARY KEY

Implement alternate candidates with appropriate UNIQUE and NOT NULL constraints, define their scope and comparison rules, preserve them when using surrogate IDs, and test lifecycle and concurrency behavior. Primary and alternate keys work together: one supplies the principal relational identity, while the others prevent duplicate representations through every additional valid identity.