DataBase Design Lesson 7 – Keys in Database Design | Dataplexa

Database Design · Lesson 7

Keys in Database Design

Master primary keys, foreign keys, and unique constraints to create reliable database structures that prevent data corruption and maintain data integrity.

Keys are the backbone of database design. They prevent duplicate data, establish relationships between tables, and guarantee data integrity. Without proper keys, your database becomes a chaotic mess of inconsistent information. Think of keys like unique identifiers in the real world. Your social security number uniquely identifies you. Your email address ensures no duplicate accounts on Netflix. BookVault needs the same system for customers, books, and orders.

Types of Keys

Database keys come in several flavors. Each serves a specific purpose in maintaining data integrity and establishing relationships.

Primary Key

Uniquely identifies each record. Cannot be NULL. Only one per table.

Foreign Key

Links to primary key in another table. Creates relationships.

Unique Key

Ensures uniqueness but allows one NULL. Multiple per table allowed.

Composite Key

Multiple columns combined to create uniqueness.

Primary Keys

A primary key is your table's unique identifier. Every row must have a different primary key value. No exceptions. No duplicates. No NULL values allowed. Why does this matter? Because databases need a way to reference specific records. When a customer places an order, the system must know exactly which customer. When you update a book's price, the database must target the correct book.

Primary Key Rules

Must be unique across all rows. Cannot contain NULL values. Should never change once assigned. Keep it simple — preferably a single column.

BookVault uses primary keys throughout its schema. The customers table uses customer_id. The books table uses book_id. Each guarantees unique identification.

-- Create BookVault customers table with primary key
-- customer_id serves as the unique identifier
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Query OK, 0 rows affected (0.02 sec)

What just happened?

The AUTO_INCREMENT automatically generates unique numbers. PRIMARY KEY enforces uniqueness and prevents NULL values. Try this: insert two customers and see how customer_id increases automatically.

Natural vs Surrogate Keys

Primary keys come in two flavors. Natural keys use existing data that's already unique. Surrogate keys are artificial values created specifically for identification.

Should BookVault use customer email addresses as primary keys? They're naturally unique. But what happens when customers want to change their email? You'd need to update every related order record. That's why surrogate keys like customer_id work better.

Surrogate Keys (Recommended)

customer_id, book_id, order_id

Never change. Simple integers. Fast joins. No business logic dependencies.

Natural Keys

email, ISBN, social_security

Can change. Complex formats. Business dependencies. Use with caution.

Foreign Keys

Foreign keys create relationships between tables. They're columns that reference primary keys in other tables. This is how BookVault connects customers to their orders, and orders to specific books. Think of foreign keys as digital threads weaving your database together. Without them, you'd have isolated tables with no connections. Customer data here, order data there, but no way to know which customer placed which order.

-- Create orders table with foreign key reference
-- customer_id references customers table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Query OK, 0 rows affected (0.03 sec)

What just happened?

The foreign key constraint prevents invalid customer_id values. You cannot create an order for customer_id 999 if customer 999 doesn't exist. Try this: attempt to insert an order with a non-existent customer_id.

Referential Integrity

Referential integrity ensures foreign keys always reference valid primary keys. It prevents orphaned records — orders without customers, order items without orders.

Honestly, most data corruption comes from skipping referential integrity. You end up with ghost orders from deleted customers and phantom order items from removed products. Foreign key constraints prevent this chaos.

Common Mistake

Creating foreign key columns without foreign key constraints. The column exists but provides no data integrity. Always use FOREIGN KEY (column) REFERENCES table(column) syntax.

Unique Constraints

Unique constraints ensure column values remain distinct across all rows. Unlike primary keys, unique constraints allow one NULL value and you can have multiple unique constraints per table. BookVault uses unique constraints on customer email addresses. Two customers cannot share the same email, but the email isn't the primary key because emails might change.

-- Create books table with multiple unique constraints
-- ISBN must be unique, title-author combination must be unique
CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    category_id INT,
    price DECIMAL(8,2) NOT NULL,
    stock_qty INT DEFAULT 0,
    published_year INT,
    UNIQUE(title, author_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Query OK, 0 rows affected (0.02 sec)

What just happened?

Two unique constraints were created. ISBN column must be unique. The combination of title and author_id must also be unique, preventing duplicate book entries. Try this: attempt to insert the same book twice.

Composite Keys

Composite keys combine multiple columns to create uniqueness. When no single column provides sufficient identification, multiple columns work together as the primary key. BookVault's order_items table uses a composite key. Each combination of order_id and book_id must be unique — preventing duplicate items within the same order.

-- Create order_items with composite primary key
-- Combination of order_id and book_id creates uniqueness
CREATE TABLE order_items (
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (order_id, book_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Query OK, 0 rows affected (0.03 sec)

What just happened?

The composite primary key prevents duplicate book entries within the same order. Order 1 can contain book 5, but cannot contain book 5 twice. Try this: insert multiple different books into the same order.

Key Selection Strategy

Choosing the right keys determines your database's reliability and performance. Bad key decisions create maintenance nightmares years later. Start with surrogate primary keys for most tables. They're simple, stable, and fast. Add unique constraints for business rules — like preventing duplicate email addresses. Use foreign keys everywhere relationships exist.

Data Insight

Amazon uses surrogate keys for 95% of their primary keys. Product IDs, customer IDs, order IDs — all artificial numbers. Natural keys like UPC codes exist as separate unique constraints.

Performance Considerations

Keys directly impact query performance. Primary keys automatically create indexes for fast lookups. Foreign keys should also be indexed for efficient joins.

Integer keys perform better than string keys. Comparing two integers takes nanoseconds. Comparing two 50-character strings takes much longer. That's why customer_id INT outperforms customer_email VARCHAR(100) for joins.

2ms

Integer key join
BookVault typical

15ms

String key join
7x slower

45ms

No indexes
22x slower

Use AUTO_INCREMENT integers for primary keys. Create indexes on foreign key columns. Keep composite keys simple — preferably 2-3 columns maximum. Test performance with realistic data volumes.

Key Relationships in Action

BookVault's complete key structure demonstrates how primary keys, foreign keys, and unique constraints work together. Each table connects to others through carefully designed key relationships. The customer places orders. Orders contain multiple items. Each item references a specific book. Books have authors and categories. Every connection uses keys to maintain data integrity.

-- Query showing key relationships in action
-- Join customers to their orders using foreign keys
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 1;

customer_id | first_name | last_name | order_id | order_date | total_amount
------------|------------|-----------|----------|------------|-------------
          1 | Sarah      | Johnson   |        1 | 2024-01-15 |        45.98
          1 | Sarah      | Johnson   |        3 | 2024-01-28 |        22.99

What just happened?

The join uses primary key (customer_id) and foreign key (customer_id in orders) to connect related data. Only valid relationships return results. Try this: query order items to see the three-table relationship chain.

Keys transform separate tables into a connected database system. Without them, you'd have data chaos. With proper keys, you get data integrity, efficient queries, and reliable relationships that scale from hundreds to millions of records.

Quiz

Up Next

Introduction to Relationships

Learn how tables connect through one-to-one, one-to-many, and many-to-many relationships using the keys you just mastered.

← Previous Course Index Next →