MySQL — Primary Key

Basic Primary Key

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Named Primary Key

CREATE TABLE employees (
    id INT,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT pk_employees PRIMARY KEY (id)
);

Composite Primary Key

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrolled_date DATE,
    PRIMARY KEY (student_id, course_id)
);

Adding Primary Key After Creation

ALTER TABLE employees ADD PRIMARY KEY (id);
-- or with a name
ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (id);

Dropping Primary Key

ALTER TABLE employees DROP PRIMARY KEY;

Auto-Increment Primary Key

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Primary Key Rules

Rule	Description
Unique	No two rows can share the same value
Not Null	Cannot be NULL
One per table	Only one primary key allowed
Indexed	Automatically creates an index

Mini Practice

Write SQL code that:

1. Creates a table with a single-column primary key
2. Creates a composite primary key
3. Adds a primary key to an existing table
4. Uses AUTO_INCREMENT with a primary key

Up Next

Continue with Foreign Key — linking tables together.