Kekkei - Advancing Financial Science For Everyone

Introduction to Primary and Foreign Keys

Real-world data is interconnected. Customers place orders. Orders contain products. Employees work in departments. How do we model these relationships in databases?

Instead of duplicating customer information in every order, we reference the customer. This is where primary and foreign keys create the connections between tables!

A column (or set of columns) that uniquely identifies each row in a table. Every table should have exactly one primary key. Values must be unique and cannot be NULL.

A column that references the primary key of another table, creating a relationship between tables. Foreign keys enforce referential integrity - they ensure relationships remain valid.

Why use primary and foreign keys?

Eliminate duplication: Store customer details once, reference many times
Maintain consistency: Update customer name in one place
Enforce integrity: Can't create an order for a non-existent customer
Model relationships: One-to-many, many-to-many connections
Enable JOINs: Link related data in queries

Analogy: Think of primary keys as unique ID cards, and foreign keys as references to those ID cards.

Primary Keys - Unique Identifiers

Every table needs a way to uniquely identify each row:

sql

-- Customers table with primary key
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    city VARCHAR(50)
);

sql

-- Insert some customers
INSERT INTO customers (name, email, city) VALUES
    ('Alice Corp', 'alice@example.com', 'New York'),
    ('Bob Industries', 'bob@example.com', 'London'),
    ('Carol Enterprises', 'carol@example.com', 'Tokyo');

SELECT * FROM customers;

Primary key characteristics:

Unique: No two rows can have the same value
Not NULL: Must always have a value
Immutable: Should never change once set
Simple: Usually a single integer column
Auto-incrementing: Database generates values automatically (1, 2, 3...)

Common naming: customer_id, order_id, product_id, user_id

Foreign Keys - Creating Relationships

Foreign keys reference primary keys in other tables, creating relationships:

sql

-- Orders table with foreign key to customers
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What this means:

customer_id in orders references customer_id in customers
Every order must have a valid customer
Can't insert order for customer_id = 999 if that customer doesn't exist
This is called referential integrity

sql

-- Insert valid orders
INSERT INTO orders (customer_id, order_date, total) VALUES
    (1, '2024-01-15', 500.00),
    (2, '2024-01-20', 350.00),
    (1, '2024-02-01', 750.00);

SELECT * FROM orders;

sql

-- Try to insert order for non-existent customer
INSERT INTO orders (customer_id, order_date, total)
VALUES (999, '2024-02-10', 100.00);

The database prevents creating an order for a non-existent customer! This is referential integrity in action.

One-to-Many Relationships

The most common relationship: one customer has many orders, one department has many employees.

Relationship Visualization

customers	orders
customer_id (PK)	←	customer_id (FK)
name	order_id (PK)
email	order_date
total

One customer → many orders:

sql

-- See customer with their orders
SELECT
    c.customer_id,
    c.name,
    c.city,
    o.order_id,
    o.order_date,
    o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

Alice (customer_id = 1) has two orders. Bob has one order. Carol has zero orders yet.

Referential Integrity Protection

Foreign keys protect data integrity in multiple ways:

1. Can't insert invalid references:

sql

-- This fails - customer 999 doesn't exist
INSERT INTO orders (customer_id, order_date, total)
VALUES (999, '2024-03-01', 200.00);

2. Can't delete referenced records (by default):

sql

-- Try to delete customer who has orders
DELETE FROM customers WHERE customer_id = 1;

Alice has orders, so we can't delete her! This prevents orphaned records (orders without a customer).

Referential integrity means relationships stay valid. The database enforces that:

Foreign key values must exist in the referenced table
Can't delete records that are still referenced
Can't update primary keys that are still referenced

ON DELETE and ON UPDATE Actions

Control what happens when referenced data changes:

Options:

CASCADE - delete/update child records automatically
SET NULL - set foreign key to NULL
RESTRICT - prevent the delete/update (default)
NO ACTION - same as RESTRICT

sql

-- Foreign key with CASCADE
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE CASCADE: When you delete a customer, all their orders are automatically deleted too.

ON UPDATE CASCADE: When you update a customer_id (rare), orders update automatically to match.

sql

-- With CASCADE, this now works
DELETE FROM customers WHERE customer_id = 1;
-- Alice's orders (order_id 1 and 3) are automatically deleted!

SELECT * FROM orders;

Be careful with CASCADE!

ON DELETE CASCADE can delete large amounts of data
Make sure this behavior is what you want
Consider soft deletes (is_deleted flag) instead for important data

ON DELETE SET NULL:

sql

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,  -- Nullable now
    order_date DATE NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL
);

Now deleting a customer sets orders' customer_id to NULL (keeps the orders, removes the link).

Multiple Foreign Keys

A table can have multiple foreign keys referencing different tables:

sql

-- Products table
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- Order items with TWO foreign keys
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

order_items links to TWO tables:

order_id references orders
product_id references products

This creates a many-to-many relationship between orders and products!

Composite Primary Keys

Sometimes you need multiple columns together as the primary key:

sql

-- Student course enrollments
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Composite primary key: (student_id, course_id) together must be unique.

Student 1 can enroll in course 101
Student 1 can enroll in course 102
But student 1 can't enroll in course 101 twice

This models: one student can take many courses, one course has many students.

Naming Conventions

Best practices for primary and foreign keys:

Primary keys:

Name: table_name_id or just id
Examples: customer_id, order_id, product_id
Type: INT with AUTO_INCREMENT
Always NOT NULL (automatically with PRIMARY KEY)

Foreign keys:

Same name as referenced primary key
Example: customer_id in orders references customer_id in customers
Makes relationships obvious
Usually NOT NULL (unless relationship is optional)

Constraint names:

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Checking Foreign Key Constraints

View foreign key information:

sql

-- MySQL - show table creation including foreign keys
SHOW CREATE TABLE orders;

-- See foreign key constraints
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'orders'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Practical Example: E-commerce Schema

Complete schema with multiple relationships:

sql

-- 1. Customers (independent table)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- 2. Orders (references customers)
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 3. Products (independent table)
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 4. Order items (references orders AND products)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Relationships:

customers → orders (one-to-many)
orders → order_items (one-to-many)
products → order_items (one-to-many)
orders ↔ products (many-to-many through order_items)

Common Mistakes to Avoid

Don't make these mistakes:

No primary key: ❌
- Every table needs a unique identifier
- Without it, can't reliably reference rows
Wrong data types: ❌
- Foreign key must match referenced primary key type
- Both should be INT if one is INT
Forgetting NOT NULL on foreign keys: ❌
- If relationship is required, foreign key should be NOT NULL
- Optional relationships can be nullable
Using CASCADE without understanding: ⚠️
- ON DELETE CASCADE deletes child records
- Make sure this is the behavior you want
No foreign key constraints: ❌
- Having a customer_id column isn't enough
- Need FOREIGN KEY constraint for integrity
Circular references: ❌
- Table A references B, B references A
- Can cause issues with inserts/deletes
Not indexing foreign keys: ⚠️
- Foreign keys should usually have indexes
- Many databases create these automatically

Key Takeaways

What you learned:

✅ PRIMARY KEY uniquely identifies each row in a table ✅ FOREIGN KEY references primary key in another table ✅ Foreign keys create relationships between tables ✅ Referential integrity keeps data consistent ✅ Can't insert foreign key value that doesn't exist ✅ Can't delete row that's still referenced (unless CASCADE) ✅ ON DELETE CASCADE - automatically delete child records ✅ ON DELETE SET NULL - set foreign key to NULL ✅ ON UPDATE CASCADE - propagate primary key changes ✅ Tables can have multiple foreign keys ✅ One-to-many is the most common relationship ✅ Composite primary keys for many-to-many relationships

Real-world applications:

Customer → Orders (one customer, many orders)
Department → Employees (one department, many employees)
Order → Order Items (one order, many line items)
Students ↔ Courses (many-to-many)
User → Comments → Replies (nested relationships)

Practice Exercise:

Create authors and books tables with one-to-many relationship
Add foreign key from books to authors
Try to insert book for non-existent author (should fail)
Create departments and employees with CASCADE delete
Build many-to-many: students, courses, enrollments
Add order of operations: customers → orders → order_items

Introduction to Primary and Foreign KeysFocusStart Focus Mode

Primary Keys - Unique IdentifiersFocusStart Focus Mode

Foreign Keys - Creating RelationshipsFocusStart Focus Mode

One-to-Many RelationshipsFocusStart Focus Mode

Referential Integrity ProtectionFocusStart Focus Mode

ON DELETE and ON UPDATE ActionsFocusStart Focus Mode

Multiple Foreign KeysFocusStart Focus Mode

Composite Primary KeysFocusStart Focus Mode

Naming ConventionsFocusStart Focus Mode

Checking Foreign Key ConstraintsFocusStart Focus Mode

Practical Example: E-commerce SchemaFocusStart Focus Mode

Common Mistakes to AvoidFocusStart Focus Mode

Key TakeawaysFocusStart Focus Mode