MySQL PRIMARY KEY

A PRIMARY KEY is a column (or a combination of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and the primary key column can never contain a NULL value. Every table should have a primary key to ensure each record is uniquely identifiable.

Why PRIMARY KEY Matters

Imagine a school with two students named "Amit Sharma." Without a unique identifier, it is impossible to distinguish between them in the database. A primary key — such as a student ID — guarantees that each student record is distinct and easy to locate.

Rules for PRIMARY KEY

• Each table can have only one primary key.
• The primary key column must have unique values.
• The primary key column cannot contain NULL.
• A primary key can consist of a single column or multiple columns (composite key).

Syntax

Single Column Primary Key — Column Level

CREATE TABLE table_name (
    column_name datatype PRIMARY KEY
);

Single Column Primary Key — Table Level

CREATE TABLE table_name (
    column_name datatype,
    CONSTRAINT constraint_name PRIMARY KEY (column_name)
);

Example: Single Column Primary Key

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    class VARCHAR(10)
);

Here, student_id is the primary key. Every student must have a unique ID, and none can be NULL.

Valid Inserts

INSERT INTO students VALUES (101, 'Neha Gupta', '10A');
INSERT INTO students VALUES (102, 'Arjun Mehta', '10B');

Invalid Inserts

-- Duplicate primary key
INSERT INTO students VALUES (101, 'Vikram Singh', '11A');

-- NULL primary key
INSERT INTO students VALUES (NULL, 'Pooja Nair', '9C');

Both will produce errors.

Composite PRIMARY KEY

A composite primary key uses two or more columns together to uniquely identify a row. Neither column alone is unique, but the combination is.

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

A student can enroll in multiple courses, and a course can have multiple students. But the same student cannot enroll in the same course more than once.

Adding PRIMARY KEY to an Existing Table

ALTER TABLE students
ADD PRIMARY KEY (student_id);

Removing PRIMARY KEY

ALTER TABLE students
DROP PRIMARY KEY;

PRIMARY KEY with AUTO_INCREMENT

Primary keys are often combined with AUTO_INCREMENT so that MySQL automatically assigns a unique number to each new row.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(8,2)
);

INSERT INTO products (product_name, price) VALUES ('Notebook', 45.00);
INSERT INTO products (product_name, price) VALUES ('Pen', 10.00);

MySQL automatically assigns product_id values 1, 2, 3, and so on.

Viewing the PRIMARY KEY

SHOW KEYS FROM students WHERE Key_name = 'PRIMARY';

Key Points

• A PRIMARY KEY uniquely identifies each row in a table.
• It cannot contain NULL and must be unique.
• Only one primary key is allowed per table.
• A composite primary key uses multiple columns together as the unique identifier.
• Primary keys are commonly combined with AUTO_INCREMENT for automatic ID generation.