MySQL PRIMARY KEY

A PRIMARY KEY is used to uniquely identify every record in a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Here, student_id uniquely identifies each student.

A PRIMARY KEY helps MySQL identify individual records.

• Identifies each row uniquely
• Prevents duplicate key values
• Does not allow NULL values
• Helps establish relationships between tables
• Can be used as a reference by foreign keys

The simplest syntax is:

column_name data_type PRIMARY KEY

Example:

student_id INT PRIMARY KEY

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

Each student must have a different student_id.

Two rows cannot have the same PRIMARY KEY value.

INSERT INTO students
(student_id, name, course)
VALUES
(1, 'Amit', 'Python');

INSERT INTO students
(student_id, name, course)
VALUES
(1, 'Rahul', 'Java');

The second INSERT fails because student_id = 1 already exists.

A PRIMARY KEY cannot contain NULL values.

INSERT INTO students
(student_id, name)
VALUES
(NULL, 'Priya');

This produces an error because the PRIMARY KEY must identify the row.

A table can have only one PRIMARY KEY constraint.

However, that PRIMARY KEY can contain more than one column. This is called a composite primary key.

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

A common pattern is to use an integer PRIMARY KEY with AUTO_INCREMENT.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100)
);

MySQL automatically generates a new ID when a row is inserted without specifying the ID.

INSERT INTO students
(name, course)
VALUES
('Amit', 'Python');

INSERT INTO students
(name, course)
VALUES
('Priya', 'Java');

MySQL generates the student_id values automatically.

The PRIMARY KEY can also be defined separately inside CREATE TABLE.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    course VARCHAR(100),
    PRIMARY KEY (student_id)
);

This is useful when defining a composite primary key or when keeping constraints together.

You can add a PRIMARY KEY to an existing table using ALTER TABLE.

ALTER TABLE students
ADD PRIMARY KEY (student_id);

The column must contain suitable unique, non-NULL values.

You can remove the PRIMARY KEY constraint using ALTER TABLE.

ALTER TABLE students
DROP PRIMARY KEY;

The table remains, but it no longer has a PRIMARY KEY.

Integer columns are commonly used as PRIMARY KEY columns.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

The employee_id uniquely identifies each employee.

A VARCHAR column can also be used as a PRIMARY KEY if its values are unique and non-NULL.

CREATE TABLE countries (
    country_code VARCHAR(3) PRIMARY KEY,
    country_name VARCHAR(100)
);

Example values could be IND, USA, and GBR.

You can use DESCRIBE to inspect a table.

DESCRIBE students;

The Key column shows PRI for a PRIMARY KEY column.

SHOW CREATE TABLE displays the CREATE TABLE statement, including its constraints.

SHOW CREATE TABLE students;

This is useful for checking how the PRIMARY KEY has been defined.

A composite PRIMARY KEY uses two or more columns together to uniquely identify a row.

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

The combination of student_id and course_id must be unique.

Consider these records:

The same student can appear multiple times because the complete combination is different.

This combination cannot be inserted twice:

student_id = 1
course_id = 101

For example:

INSERT INTO student_courses
(student_id, course_id)
VALUES
(1, 101);

INSERT INTO student_courses
(student_id, course_id)
VALUES
(1, 101);

The second record violates the PRIMARY KEY constraint.

A PRIMARY KEY can be referenced by a FOREIGN KEY in another table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
);

Here, students.student_id is the PRIMARY KEY and fees.student_id references it.

MySQL automatically creates a unique index for a PRIMARY KEY.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

This supports efficient identification and lookup of rows.

A good PRIMARY KEY should generally be:

• Unique
• Stable
• Not NULL
• As simple as practical
• Suitable for identifying one row

An integer AUTO_INCREMENT column is a common choice for many application tables.

Do not manually insert duplicate PRIMARY KEY values.

INSERT INTO students
(student_id, name)
VALUES
(1, 'Amit');

INSERT INTO students
(student_id, name)
VALUES
(1, 'Rahul');

The second INSERT fails because 1 is already used.

A PRIMARY KEY column cannot contain NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

The following is invalid:

INSERT INTO students
(student_id, name)
VALUES
(NULL, 'Amit');

• Trying to insert duplicate PRIMARY KEY values
• Trying to insert NULL into a PRIMARY KEY
• Choosing a value that is not stable
• Defining more than one PRIMARY KEY constraint
• Adding a PRIMARY KEY when existing data contains duplicates
• Ignoring relationships with foreign keys
• Using an unnecessarily large composite key

Suppose a table already exists:

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    course VARCHAR(100)
);

You can add the PRIMARY KEY later:

ALTER TABLE students
ADD PRIMARY KEY (student_id);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    mobile VARCHAR(15)
);

INSERT INTO students
(name, course, mobile)
VALUES
('Amit Kumar', 'Python', '9876543210'),
('Priya Singh', 'Java', '9876543211'),
('Rahul Kumar', 'PHP', '9876543212');

SELECT *
FROM students;

Here, student_id uniquely identifies every student.

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

This design allows one student to enroll in multiple courses while preventing the same student from being enrolled in the same course more than once.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, fee)
VALUES
('Amit', 'Python', 15000.00),
('Priya', 'Java', 18000.00),
('Rahul', 'PHP', 12000.00);

SELECT *
FROM students;

DESCRIBE students;

SHOW CREATE TABLE students;

The student_id column uniquely identifies each student and automatically generates IDs for new records.