Alternate Key

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);

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

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.

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)
);