MySQL PRIMARY KEY

Uniquely identifying each record in your table

🔑 What is PRIMARY KEY?

A PRIMARY KEY uniquely identifies each record in a table. It combines NOT NULL and UNIQUE constraints, ensuring every row has a distinct identifier that cannot be empty or duplicated. 


-- Creating a table with PRIMARY KEY
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Output:

Table 'students' created with PRIMARY KEY on student_id.

Key PRIMARY KEY Concepts

🆔

Unique Identifier

Each row has unique ID 

id INT PRIMARY KEY
🚫

No NULL Values

Must always have a value 

user_id INT PRIMARY KEY
1️⃣

One Per Table

Only one PRIMARY KEY allowed 

PRIMARY KEY (id)
⚡

Fast Lookups

Automatically indexed 

product_id INT PRIMARY KEY

🔹 Creating PRIMARY KEY

Define a PRIMARY KEY when creating a table to establish a unique identifier for each record. This is essential for database design as it enables efficient data retrieval and establishes relationships between tables through foreign keys. 

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

Output:

✓ Table created with PRIMARY KEY on employee_id

🔹 PRIMARY KEY with AUTO_INCREMENT

AUTO_INCREMENT automatically generates sequential numbers for the PRIMARY KEY, eliminating the need to manually assign IDs. This is the most common pattern for primary keys, ensuring each new record gets a unique identifier without user intervention. 

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(10,2)
);

Output:

✓ Table created with auto-incrementing PRIMARY KEY

🔹 Composite PRIMARY KEY

A composite PRIMARY KEY uses multiple columns together as the unique identifier. This is useful for junction tables or when a single column cannot uniquely identify a record, like combining student and course IDs for enrollment records. 

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

Output:

✓ Composite PRIMARY KEY created on (order_id, product_id)

🔹 Named PRIMARY KEY Constraint

Naming your PRIMARY KEY constraint provides better documentation and makes it easier to reference in error messages or when modifying the constraint. Use the CONSTRAINT keyword followed by a descriptive name for clarity in complex databases. 

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    category VARCHAR(50),
    CONSTRAINT pk_product PRIMARY KEY (product_id)
);

Output:

✓ Table created with named PRIMARY KEY constraint 'pk_product'

🔹 Adding PRIMARY KEY to Existing Table

You can add a PRIMARY KEY to an existing table using ALTER TABLE. Before adding, ensure the column has no NULL or duplicate values, as PRIMARY KEY requires both uniqueness and non-null values for all existing records. 

-- Add PRIMARY KEY to existing table
ALTER TABLE customers 
ADD PRIMARY KEY (customer_id);

-- Add composite PRIMARY KEY
ALTER TABLE enrollments 
ADD PRIMARY KEY (student_id, course_id);

Output:

✓ PRIMARY KEY added successfully

🔹 Dropping PRIMARY KEY

Remove a PRIMARY KEY constraint when you need to restructure your table or change the primary key column. Note that dropping a PRIMARY KEY also removes its associated index, which may affect query performance temporarily. 

-- Drop PRIMARY KEY constraint
ALTER TABLE products 
DROP PRIMARY KEY;

Output:

✓ PRIMARY KEY dropped successfully

🔹 PRIMARY KEY Best Practices

Choose simple, stable values for PRIMARY KEYs that won't change over time. Integer AUTO_INCREMENT columns are ideal for most tables, while composite keys work well for many-to-many relationships. Avoid using business data like email addresses as primary keys. 

-- Good: Simple integer PRIMARY KEY
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50)
);

-- Good: Composite key for junction table
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

Output:

✓ Tables created following PRIMARY KEY best practices

🧠 Test Your Knowledge

How many PRIMARY KEYs can a table have?