Database Management SystemsKeys

Keys in the Relational Model

LevelBeginner

Duration60 mins

TopicKeys

4 / 5

Alternate Key

The Keys Not Chosen

When a relation has multiple candidate keys, we designate one as the primary key. But what happens to the others? They don't simply disappear or lose their significance. These remaining candidate keys become alternate keys—equally valid identifiers that maintain their uniqueness guarantees.

Consider our familiar Employee example with candidate keys {EmployeeID}, {SSN}, and {Email}. If we choose EmployeeID as the primary key:

{SSN} becomes an alternate key
{Email} becomes an alternate key

Both still uniquely identify employees. Both still require uniqueness enforcement. They simply weren't designated as primary.

Alternate keys are not second-class citizens in your schema—they're essential components of data integrity, often representing real-world identification systems that exist independently of your database design.

What You Will Learn

By the end of this page, you will understand the formal definition and purpose of alternate keys, master the implementation of alternate key constraints in SQL, appreciate the practical applications and importance of alternate keys in database design, and recognize the relationship between alternate keys, unique constraints, and business rules.

Formal Definition of Alternate Key

Definition: Alternate Key

An alternate key (also called a secondary key) is a candidate key that was not selected as the primary key. It possesses all the properties of a candidate key—uniqueness and minimality—but serves as an alternative means of tuple identification.

Mathematical relationship:

Let R be a relation with candidate keys CK = {K₁, K₂, ..., Kₙ} where n > 1 (more than one candidate key exists).

If PK is designated as the primary key, then:

Alternate Keys = CK - {PK} = {K₁, K₂, ..., Kₙ} - {PK}

Each alternate key AK satisfies:

AK → R (superkey property)
∀A ∈ AK: (AK - {A})⁺ ≠ R (minimality)
AK ≠ PK (not the primary key)

Terminological equivalences:

Term	Definition	Usage
Alternate Key	Candidate key that's not primary	Formal database theory
Secondary Key	Same as alternate key	Some textbooks
Unique Key	Any attribute set with uniqueness constraint	SQL implementations (broader)
Natural Key	Domain-meaningful identifier	When contrasting with surrogate

Note: In SQL, the term "UNIQUE constraint" is often used for alternate keys, but UNIQUE can apply to any attribute set, not just candidate keys. A candidate key that becomes an alternate key is typically enforced via UNIQUE.

Alternate ≠ Optional

The term 'alternate' might suggest these keys are less important or optional. This is incorrect! Alternate keys are critical for data integrity—they represent real-world uniqueness requirements that must be enforced regardless of which key was designated primary.

Purpose and Importance

Alternate keys serve several crucial purposes in database systems, extending far beyond simply being 'the keys we didn't pick'.

Critical Functions of Alternate Keys

•Data Integrity Enforcement: They prevent duplicate entries based on real-world identifiers. Even if EmployeeID is primary, you can't have two employees with the same SSN—that represents a real-world impossibility.
•Alternative Access Paths: Users and applications may query by alternate keys. 'Find employee by SSN' is a legitimate query pattern even when EmployeeID is primary.
•External System Integration: External systems may identify entities differently. Government systems use SSN; your HR system uses EmployeeID. Alternate keys bridge these systems.
•Business Rule Encoding: Many alternate keys represent business rules—'no two products can have the same SKU' or 'email addresses must be unique per customer'.
•Data Migration and Merging: When integrating databases, alternate keys help identify duplicate records across systems with different primary key schemes.
•Historical Compatibility: Legacy systems may reference records by alternate keys. Maintaining these keys preserves backward compatibility.

Example: Multi-System Identity

Consider a Product table in a retail system:

ProductID (PK)	SKU (AK)	UPC (AK)	ASIN (AK)	Name
1001	WDG-BLU-001	012345678901	B07XYZ123	Blue Widget
1002	WDG-RED-001	012345678902	B07XYZ124	Red Widget
1003	GAD-GRN-001	012345678903	B07ABC789	Green Gadget

This product has:

ProductID: Internal database identity (primary key)
SKU: Warehouse and inventory system identifier (alternate key)
UPC: Point-of-sale scanner code (alternate key)
ASIN: Amazon product code for e-commerce integration (alternate key)

Each alternate key represents a different domain's perspective:

The warehouse sees SKU
The barcode scanner sees UPC
Amazon's API sees ASIN
The internal database sees ProductID

All four keys are valid identifiers. One is primary; three are alternate. All require uniqueness enforcement.

Implementing Alternate Keys in SQL

In SQL, alternate keys are implemented using the UNIQUE constraint. This ensures the DBMS enforces uniqueness on the specified column(s), just as it does for primary keys.

alternate_key_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- Inline UNIQUE constraint (single column)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    SSN CHAR(11) NOT NULL UNIQUE,        -- Alternate key (inline)
    Email VARCHAR(255) NOT NULL UNIQUE,   -- Another alternate key
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL
);
 
-- Table-level UNIQUE constraint (preferred for clarity)
CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    SSN CHAR(11) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    PRIMARY KEY (EmployeeID),
    UNIQUE (SSN),                         -- Named implicitly by DBMS
    UNIQUE (Email)
);
 
-- Named UNIQUE constraints (best practice)
CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    SSN CHAR(11) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    CONSTRAINT pk_employee PRIMARY KEY (EmployeeID),
    CONSTRAINT ak_employee_ssn UNIQUE (SSN),
    CONSTRAINT ak_employee_email UNIQUE (Email)
);
 
-- Composite alternate key
CREATE TABLE CourseOffering (
    OfferingID INT PRIMARY KEY,           -- Surrogate primary key
    CourseCode VARCHAR(10) NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    Year INT NOT NULL,
    InstructorID INT NOT NULL,
    RoomID INT NOT NULL,
    CONSTRAINT ak_offering_natural UNIQUE (CourseCode, Semester, Year)
);
 
-- Adding alternate key to existing table
ALTER TABLE Employee
ADD CONSTRAINT ak_employee_badge UNIQUE (BadgeNumber);

Naming Convention Best Practice

Use consistent naming for constraints: 'pk_' prefix for primary keys, 'ak_' or 'uq_' prefix for alternate/unique keys, 'fk_' for foreign keys. This makes schema navigation and error messages clearer. Example: 'ak_employee_ssn' immediately indicates an alternate key on SSN in the Employee table.

NULL Values and Alternate Keys

One critical difference between primary keys and alternate keys involves NULL handling. While primary keys never allow NULLs (entity integrity), alternate keys have more nuanced behavior that varies by DBMS.

The UNIQUE + NULL Complexity

The SQL standard allows NULL values in UNIQUE columns, and most DBMSs permit multiple NULLs in a UNIQUE column (since NULL ≠ NULL). However, this can undermine the candidate key property. True alternate keys (candidate keys) should have NOT NULL constraints alongside UNIQUE.

DBMS-Specific NULL Behavior in UNIQUE Columns:

NULL Handling in UNIQUE Constraints
DBMS	Multiple NULLs Allowed?	Notes
PostgreSQL	Yes (by default)	Use 'UNIQUE NULLS NOT DISTINCT' (v15+) to disallow
MySQL	Yes	NULLs are considered distinct
SQL Server	No (by default)	UNIQUE constraint treats NULL as a value
Oracle	Yes	Composite: NULL not included in uniqueness check
SQLite	Yes	Each NULL is distinct

Best Practice: Always Pair UNIQUE with NOT NULL for True Alternate Keys

-- THIS IS A TRUE ALTERNATE KEY (candidate key property preserved)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    SSN CHAR(11) NOT NULL,           -- NOT NULL + UNIQUE = true alternate key
    CONSTRAINT ak_ssn UNIQUE (SSN)
);

-- THIS IS JUST A UNIQUENESS CHECK (not a true alternate key)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    LicenseNumber VARCHAR(20),       -- Nullable, so not a candidate key
    CONSTRAINT uq_license UNIQUE (LicenseNumber)  -- Allows NULLs
);

When to allow NULL in UNIQUE columns:

Sometimes you need uniqueness among values that exist, but the attribute is optional:

DriversLicense: Not all employees have one, but those who do must have unique licenses
PreviousEmployeeID: Only re-hires have this value
MergedFromID: Only migrated records have this reference

These are unique constraints, not alternate keys. The distinction matters for theoretical correctness.

Indexing and Performance

Alternate keys have direct implications for database indexing and query performance. Understanding these implications is essential for effective database design.

Automatic Index Creation

Most DBMSs automatically create a unique index to enforce UNIQUE constraints. This means your alternate keys are already indexed—queries filtering by alternate keys benefit from index-based access (typically O(log n) via B-tree).

Index creation behavior by DBMS:

DBMS	Auto-Creates Index for UNIQUE?	Index Type	Customizable?
PostgreSQL	Yes	B-tree	Yes (can specify type)
MySQL	Yes	B-tree (InnoDB)	Limited
SQL Server	Yes	Non-clustered B-tree	Yes
Oracle	Yes	B-tree	Yes (can be preexisting)
SQLite	Yes	B-tree	No

Performance considerations:

Alternate Key Performance Factors

•Query benefits: Lookups by alternate key are fast (index scan vs table scan). 'SELECT * WHERE SSN = ?' uses the unique index.
•Insert overhead: Each alternate key index must be updated on INSERT. More alternate keys = more insert overhead.
•Update cost: Changing an alternate key value requires index update. Stable alternate keys (like SSN) minimize this.
•Storage cost: Each unique index consumes storage. Trade-off between query speed and disk usage.
•Join efficiency: If other tables reference by alternate key (unusual but possible), the index supports efficient joins.

alternate_key_query_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- These queries benefit from alternate key indexes:
 
-- Lookup by SSN (alternate key)
EXPLAIN ANALYZE
SELECT * FROM Employee WHERE SSN = '123-45-6789';
-- Uses index 'ak_employee_ssn' -> Index Scan
 
-- Lookup by Email (alternate key)
EXPLAIN ANALYZE  
SELECT * FROM Employee WHERE Email = 'john.doe@company.com';
-- Uses index 'ak_employee_email' -> Index Scan
 
-- Existence check during insert (enforces uniqueness)
INSERT INTO Employee (EmployeeID, SSN, Email, FirstName, LastName, HireDate)
VALUES (1001, '123-45-6789', 'jane.doe@company.com', 'Jane', 'Doe', '2024-01-15');
-- DBMS checks ak_employee_ssn index -> unique constraint violation if exists
 
-- Covering index opportunity (PostgreSQL/SQL Server)
CREATE UNIQUE INDEX ak_employee_email_covering
ON Employee (Email) 
INCLUDE (FirstName, LastName);
-- Now SELECT FirstName, LastName WHERE Email = ? doesn't need table access

Alternate Key Design Patterns

Several common patterns emerge in how experienced database designers use alternate keys. Understanding these patterns helps you apply alternate keys effectively.

Common Alternate Key Patterns

•Surrogate + Natural Pattern: Use a surrogate primary key (auto-increment) for internal relationships, but maintain the natural key as an alternate key for business logic and external integration. This is the most common pattern. Example: CustomerID (PK, surrogate) + TaxID (AK, natural).
•Multi-System Identity Pattern: When entities exist in multiple external systems, each system's identifier becomes an alternate key. Example: Product with ProductID (PK), SKU (AK for warehouse), UPC (AK for retail), ASIN (AK for Amazon).
•Historical + Current Pattern: When identifiers change over time but old identifiers must remain valid. Example: Order with OrderID (PK), LegacyOrderNumber (AK for compatibility with old system).
•Slug/URL Pattern: Human-readable identifiers for web applications. Example: BlogPost with PostID (PK, efficient for joins), Slug (AK, 'my-awesome-article' for URLs).
•Code + Name Pattern: Both a code and a name must be unique. Example: Country with CountryID (PK), CountryCode (AK, 'US'), CountryName (AK, 'United States').

Complete Example: Multi-Pattern Implementation

alternate_key_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- E-commerce Product Table with Multiple Alternate Keys
CREATE TABLE Product (
    -- Surrogate Primary Key (internal use)
    ProductID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    
    -- Natural Business Key (Alternate Key #1)
    SKU VARCHAR(20) NOT NULL,
    
    -- External System Identifiers (Alternate Keys #2-4)
    UPC CHAR(12),                    -- Universal Product Code
    EAN CHAR(13),                    -- European Article Number
    ASIN VARCHAR(10),                -- Amazon Standard Identification
    
    -- Human-Readable Identifier (Alternate Key #5)
    Slug VARCHAR(100) NOT NULL,
    
    -- Product Details
    Name VARCHAR(200) NOT NULL,
    Description TEXT,
    Price DECIMAL(10,2) NOT NULL,
    Category VARCHAR(50) NOT NULL,
    
    -- Timestamps
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Alternate Key Constraints
    CONSTRAINT ak_product_sku UNIQUE (SKU),
    CONSTRAINT ak_product_upc UNIQUE (UPC),
    CONSTRAINT ak_product_ean UNIQUE (EAN),
    CONSTRAINT ak_product_asin UNIQUE (ASIN),
    CONSTRAINT ak_product_slug UNIQUE (Slug)
);
 
-- Commentary:
-- ProductID: Used in foreign keys (OrderLine.ProductID, InventoryItem.ProductID)
-- SKU: Used by warehouse management system
-- UPC: Used by POS systems for barcode scanning
-- EAN: Used by European retail partners
-- ASIN: Used by Amazon integration API
-- Slug: Used in URLs (/products/blue-widget-large)
-- 
-- Each alternate key enables different use cases while ProductID
-- provides efficient internal relationships.

Alternate Keys and Foreign Keys

While primary keys are the conventional target for foreign key references, alternate keys can also serve as foreign key targets. This capability is sometimes necessary for legacy integration or specific design requirements.

Foreign Keys Referencing Alternate Keys

SQL allows foreign keys to reference any column(s) with a UNIQUE or PRIMARY KEY constraint. This means alternate keys can be referenced by foreign keys from other tables, though this is less common than referencing primary keys.

alternate_key_foreign_key.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Parent table with primary and alternate keys
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    TaxID CHAR(11) NOT NULL UNIQUE,     -- Alternate key
    Email VARCHAR(255) NOT NULL UNIQUE,  -- Alternate key
    Name VARCHAR(100) NOT NULL
);
 
-- Child referencing PRIMARY KEY (conventional)
CREATE TABLE Order_ByID (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
 
-- Child referencing ALTERNATE KEY (special cases)
CREATE TABLE TaxDocument (
    DocumentID INT PRIMARY KEY,
    TaxID CHAR(11) NOT NULL,
    DocumentType VARCHAR(50) NOT NULL,
    FilingDate DATE NOT NULL,
    -- Foreign key references alternate key, not primary key
    FOREIGN KEY (TaxID) REFERENCES Customer(TaxID)
);
 
-- Another example: Legacy system integration
CREATE TABLE LegacyOrder (
    LegacyOrderID INT PRIMARY KEY,
    CustomerEmail VARCHAR(255) NOT NULL,
    OrderData TEXT,
    -- Reference by email because legacy system didn't use CustomerID
    FOREIGN KEY (CustomerEmail) REFERENCES Customer(Email)
);

When to Reference Alternate Keys

•Legacy integration requiring specific identifiers
•Cross-system references using industry codes
•External system integration (their ID → your alternate key)
•Natural key relationships for business logic

When to Avoid

•When primary key is available and stable
•When alternate key might change (cascades become expensive)
•When alternate key is composite (joins become complex)
•When performance is critical (PK is usually clustered index)

Update Cascade Implications

If a foreign key references an alternate key, and that alternate key value changes, ON UPDATE CASCADE must propagate the change to all referencing tables. This can be expensive for frequently-updated alternate keys. Prefer immutable alternate keys as FK targets.

Summary: Alternate Key Mastery

We've thoroughly explored alternate keys—the candidate keys that support data integrity and provide alternative identification paths alongside the primary key. Let's consolidate the key insights:

Key Takeaways

•Alternate keys are non-primary candidate keys — They possess uniqueness and minimality but weren't designated as primary.
•They serve critical integrity functions — Alternate keys enforce real-world uniqueness requirements that exist independently of primary key selection.
•Implemented via UNIQUE constraints — SQL uses UNIQUE (ideally with NOT NULL) to enforce alternate key properties.
•NULL behavior varies by DBMS — True alternate keys should always include NOT NULL; otherwise they're just uniqueness checks.
•Automatic indexing benefits queries — Most DBMSs create indexes for UNIQUE constraints, enabling efficient lookups.
•Common patterns guide design — Surrogate+Natural, Multi-System Identity, and Slug patterns address recurring requirements.

What's next:

Now that we understand primary and alternate keys within a single relation, we'll explore foreign keys—the mechanism that connects relations together. Foreign keys are the foundation of referential integrity and enable the relational model's power to represent complex, interconnected data.

Page Complete

You now understand alternate keys as integral components of relational integrity. You can implement alternate keys using UNIQUE constraints, apply common design patterns effectively, understand NULL handling nuances across DBMSs, and appreciate the indexing and performance implications. Next, we'll explore foreign keys—the relational glue that connects tables together.

4 / 5

Loading learning content...

Database Management SystemsKeys

Keys in the Relational Model

LevelBeginner

Duration60 mins

TopicKeys

4 / 5

Alternate Key

The Keys Not Chosen

Consider our familiar Employee example with candidate keys {EmployeeID}, {SSN}, and {Email}. If we choose EmployeeID as the primary key:

{SSN} becomes an alternate key
{Email} becomes an alternate key

Both still uniquely identify employees. Both still require uniqueness enforcement. They simply weren't designated as primary.

What You Will Learn

Formal Definition of Alternate Key

Definition: Alternate Key

Mathematical relationship:

Let R be a relation with candidate keys CK = {K₁, K₂, ..., Kₙ} where n > 1 (more than one candidate key exists).

If PK is designated as the primary key, then:

Alternate Keys = CK - {PK} = {K₁, K₂, ..., Kₙ} - {PK}

Each alternate key AK satisfies:

AK → R (superkey property)
∀A ∈ AK: (AK - {A})⁺ ≠ R (minimality)
AK ≠ PK (not the primary key)

Terminological equivalences:

Term	Definition	Usage
Alternate Key	Candidate key that's not primary	Formal database theory
Secondary Key	Same as alternate key	Some textbooks
Unique Key	Any attribute set with uniqueness constraint	SQL implementations (broader)
Natural Key	Domain-meaningful identifier	When contrasting with surrogate

Alternate ≠ Optional

Purpose and Importance

Alternate keys serve several crucial purposes in database systems, extending far beyond simply being 'the keys we didn't pick'.

Critical Functions of Alternate Keys

•Data Integrity Enforcement: They prevent duplicate entries based on real-world identifiers. Even if EmployeeID is primary, you can't have two employees with the same SSN—that represents a real-world impossibility.
•Alternative Access Paths: Users and applications may query by alternate keys. 'Find employee by SSN' is a legitimate query pattern even when EmployeeID is primary.
•External System Integration: External systems may identify entities differently. Government systems use SSN; your HR system uses EmployeeID. Alternate keys bridge these systems.
•Business Rule Encoding: Many alternate keys represent business rules—'no two products can have the same SKU' or 'email addresses must be unique per customer'.
•Data Migration and Merging: When integrating databases, alternate keys help identify duplicate records across systems with different primary key schemes.
•Historical Compatibility: Legacy systems may reference records by alternate keys. Maintaining these keys preserves backward compatibility.

Example: Multi-System Identity

Consider a Product table in a retail system:

ProductID (PK)	SKU (AK)	UPC (AK)	ASIN (AK)	Name
1001	WDG-BLU-001	012345678901	B07XYZ123	Blue Widget
1002	WDG-RED-001	012345678902	B07XYZ124	Red Widget
1003	GAD-GRN-001	012345678903	B07ABC789	Green Gadget

This product has:

ProductID: Internal database identity (primary key)
SKU: Warehouse and inventory system identifier (alternate key)
UPC: Point-of-sale scanner code (alternate key)
ASIN: Amazon product code for e-commerce integration (alternate key)

Each alternate key represents a different domain's perspective:

The warehouse sees SKU
The barcode scanner sees UPC
Amazon's API sees ASIN
The internal database sees ProductID

All four keys are valid identifiers. One is primary; three are alternate. All require uniqueness enforcement.

Implementing Alternate Keys in SQL

In SQL, alternate keys are implemented using the UNIQUE constraint. This ensures the DBMS enforces uniqueness on the specified column(s), just as it does for primary keys.

alternate_key_implementation.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- Inline UNIQUE constraint (single column)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    SSN CHAR(11) NOT NULL UNIQUE,        -- Alternate key (inline)
    Email VARCHAR(255) NOT NULL UNIQUE,   -- Another alternate key
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL
);
 
-- Table-level UNIQUE constraint (preferred for clarity)
CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    SSN CHAR(11) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    PRIMARY KEY (EmployeeID),
    UNIQUE (SSN),                         -- Named implicitly by DBMS
    UNIQUE (Email)
);
 
-- Named UNIQUE constraints (best practice)
CREATE TABLE Employee (
    EmployeeID INT NOT NULL,
    SSN CHAR(11) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    HireDate DATE NOT NULL,
    CONSTRAINT pk_employee PRIMARY KEY (EmployeeID),
    CONSTRAINT ak_employee_ssn UNIQUE (SSN),
    CONSTRAINT ak_employee_email UNIQUE (Email)
);
 
-- Composite alternate key
CREATE TABLE CourseOffering (
    OfferingID INT PRIMARY KEY,           -- Surrogate primary key
    CourseCode VARCHAR(10) NOT NULL,
    Semester VARCHAR(20) NOT NULL,
    Year INT NOT NULL,
    InstructorID INT NOT NULL,
    RoomID INT NOT NULL,
    CONSTRAINT ak_offering_natural UNIQUE (CourseCode, Semester, Year)
);
 
-- Adding alternate key to existing table
ALTER TABLE Employee
ADD CONSTRAINT ak_employee_badge UNIQUE (BadgeNumber);

Naming Convention Best Practice

NULL Values and Alternate Keys

The UNIQUE + NULL Complexity

DBMS-Specific NULL Behavior in UNIQUE Columns:

NULL Handling in UNIQUE Constraints
DBMS	Multiple NULLs Allowed?	Notes
PostgreSQL	Yes (by default)	Use 'UNIQUE NULLS NOT DISTINCT' (v15+) to disallow
MySQL	Yes	NULLs are considered distinct
SQL Server	No (by default)	UNIQUE constraint treats NULL as a value
Oracle	Yes	Composite: NULL not included in uniqueness check
SQLite	Yes	Each NULL is distinct

Best Practice: Always Pair UNIQUE with NOT NULL for True Alternate Keys

-- THIS IS A TRUE ALTERNATE KEY (candidate key property preserved)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    SSN CHAR(11) NOT NULL,           -- NOT NULL + UNIQUE = true alternate key
    CONSTRAINT ak_ssn UNIQUE (SSN)
);

-- THIS IS JUST A UNIQUENESS CHECK (not a true alternate key)
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    LicenseNumber VARCHAR(20),       -- Nullable, so not a candidate key
    CONSTRAINT uq_license UNIQUE (LicenseNumber)  -- Allows NULLs
);

When to allow NULL in UNIQUE columns:

Sometimes you need uniqueness among values that exist, but the attribute is optional:

DriversLicense: Not all employees have one, but those who do must have unique licenses
PreviousEmployeeID: Only re-hires have this value
MergedFromID: Only migrated records have this reference

These are unique constraints, not alternate keys. The distinction matters for theoretical correctness.

Indexing and Performance

Alternate keys have direct implications for database indexing and query performance. Understanding these implications is essential for effective database design.

Automatic Index Creation

Index creation behavior by DBMS:

DBMS	Auto-Creates Index for UNIQUE?	Index Type	Customizable?
PostgreSQL	Yes	B-tree	Yes (can specify type)
MySQL	Yes	B-tree (InnoDB)	Limited
SQL Server	Yes	Non-clustered B-tree	Yes
Oracle	Yes	B-tree	Yes (can be preexisting)
SQLite	Yes	B-tree	No

Performance considerations:

Alternate Key Performance Factors

•Query benefits: Lookups by alternate key are fast (index scan vs table scan). 'SELECT * WHERE SSN = ?' uses the unique index.
•Insert overhead: Each alternate key index must be updated on INSERT. More alternate keys = more insert overhead.
•Update cost: Changing an alternate key value requires index update. Stable alternate keys (like SSN) minimize this.
•Storage cost: Each unique index consumes storage. Trade-off between query speed and disk usage.
•Join efficiency: If other tables reference by alternate key (unusual but possible), the index supports efficient joins.

alternate_key_query_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- These queries benefit from alternate key indexes:
 
-- Lookup by SSN (alternate key)
EXPLAIN ANALYZE
SELECT * FROM Employee WHERE SSN = '123-45-6789';
-- Uses index 'ak_employee_ssn' -> Index Scan
 
-- Lookup by Email (alternate key)
EXPLAIN ANALYZE  
SELECT * FROM Employee WHERE Email = 'john.doe@company.com';
-- Uses index 'ak_employee_email' -> Index Scan
 
-- Existence check during insert (enforces uniqueness)
INSERT INTO Employee (EmployeeID, SSN, Email, FirstName, LastName, HireDate)
VALUES (1001, '123-45-6789', 'jane.doe@company.com', 'Jane', 'Doe', '2024-01-15');
-- DBMS checks ak_employee_ssn index -> unique constraint violation if exists
 
-- Covering index opportunity (PostgreSQL/SQL Server)
CREATE UNIQUE INDEX ak_employee_email_covering
ON Employee (Email) 
INCLUDE (FirstName, LastName);
-- Now SELECT FirstName, LastName WHERE Email = ? doesn't need table access

Alternate Key Design Patterns

Several common patterns emerge in how experienced database designers use alternate keys. Understanding these patterns helps you apply alternate keys effectively.

Common Alternate Key Patterns

•Surrogate + Natural Pattern: Use a surrogate primary key (auto-increment) for internal relationships, but maintain the natural key as an alternate key for business logic and external integration. This is the most common pattern. Example: CustomerID (PK, surrogate) + TaxID (AK, natural).
•Multi-System Identity Pattern: When entities exist in multiple external systems, each system's identifier becomes an alternate key. Example: Product with ProductID (PK), SKU (AK for warehouse), UPC (AK for retail), ASIN (AK for Amazon).
•Historical + Current Pattern: When identifiers change over time but old identifiers must remain valid. Example: Order with OrderID (PK), LegacyOrderNumber (AK for compatibility with old system).
•Slug/URL Pattern: Human-readable identifiers for web applications. Example: BlogPost with PostID (PK, efficient for joins), Slug (AK, 'my-awesome-article' for URLs).
•Code + Name Pattern: Both a code and a name must be unique. Example: Country with CountryID (PK), CountryCode (AK, 'US'), CountryName (AK, 'United States').

Complete Example: Multi-Pattern Implementation

alternate_key_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- E-commerce Product Table with Multiple Alternate Keys
CREATE TABLE Product (
    -- Surrogate Primary Key (internal use)
    ProductID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    
    -- Natural Business Key (Alternate Key #1)
    SKU VARCHAR(20) NOT NULL,
    
    -- External System Identifiers (Alternate Keys #2-4)
    UPC CHAR(12),                    -- Universal Product Code
    EAN CHAR(13),                    -- European Article Number
    ASIN VARCHAR(10),                -- Amazon Standard Identification
    
    -- Human-Readable Identifier (Alternate Key #5)
    Slug VARCHAR(100) NOT NULL,
    
    -- Product Details
    Name VARCHAR(200) NOT NULL,
    Description TEXT,
    Price DECIMAL(10,2) NOT NULL,
    Category VARCHAR(50) NOT NULL,
    
    -- Timestamps
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Alternate Key Constraints
    CONSTRAINT ak_product_sku UNIQUE (SKU),
    CONSTRAINT ak_product_upc UNIQUE (UPC),
    CONSTRAINT ak_product_ean UNIQUE (EAN),
    CONSTRAINT ak_product_asin UNIQUE (ASIN),
    CONSTRAINT ak_product_slug UNIQUE (Slug)
);
 
-- Commentary:
-- ProductID: Used in foreign keys (OrderLine.ProductID, InventoryItem.ProductID)
-- SKU: Used by warehouse management system
-- UPC: Used by POS systems for barcode scanning
-- EAN: Used by European retail partners
-- ASIN: Used by Amazon integration API
-- Slug: Used in URLs (/products/blue-widget-large)
-- 
-- Each alternate key enables different use cases while ProductID
-- provides efficient internal relationships.

Alternate Keys and Foreign Keys

Foreign Keys Referencing Alternate Keys

alternate_key_foreign_key.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Parent table with primary and alternate keys
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    TaxID CHAR(11) NOT NULL UNIQUE,     -- Alternate key
    Email VARCHAR(255) NOT NULL UNIQUE,  -- Alternate key
    Name VARCHAR(100) NOT NULL
);
 
-- Child referencing PRIMARY KEY (conventional)
CREATE TABLE Order_ByID (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
 
-- Child referencing ALTERNATE KEY (special cases)
CREATE TABLE TaxDocument (
    DocumentID INT PRIMARY KEY,
    TaxID CHAR(11) NOT NULL,
    DocumentType VARCHAR(50) NOT NULL,
    FilingDate DATE NOT NULL,
    -- Foreign key references alternate key, not primary key
    FOREIGN KEY (TaxID) REFERENCES Customer(TaxID)
);
 
-- Another example: Legacy system integration
CREATE TABLE LegacyOrder (
    LegacyOrderID INT PRIMARY KEY,
    CustomerEmail VARCHAR(255) NOT NULL,
    OrderData TEXT,
    -- Reference by email because legacy system didn't use CustomerID
    FOREIGN KEY (CustomerEmail) REFERENCES Customer(Email)
);

When to Reference Alternate Keys

•Legacy integration requiring specific identifiers
•Cross-system references using industry codes
•External system integration (their ID → your alternate key)
•Natural key relationships for business logic

When to Avoid

•When primary key is available and stable
•When alternate key might change (cascades become expensive)
•When alternate key is composite (joins become complex)
•When performance is critical (PK is usually clustered index)

Update Cascade Implications

Summary: Alternate Key Mastery

We've thoroughly explored alternate keys—the candidate keys that support data integrity and provide alternative identification paths alongside the primary key. Let's consolidate the key insights:

Key Takeaways

•Alternate keys are non-primary candidate keys — They possess uniqueness and minimality but weren't designated as primary.
•They serve critical integrity functions — Alternate keys enforce real-world uniqueness requirements that exist independently of primary key selection.
•Implemented via UNIQUE constraints — SQL uses UNIQUE (ideally with NOT NULL) to enforce alternate key properties.
•NULL behavior varies by DBMS — True alternate keys should always include NOT NULL; otherwise they're just uniqueness checks.
•Automatic indexing benefits queries — Most DBMSs create indexes for UNIQUE constraints, enabling efficient lookups.
•Common patterns guide design — Surrogate+Natural, Multi-System Identity, and Slug patterns address recurring requirements.

What's next:

Page Complete

4 / 5