How to add a primary key to a MySQL table?

In MySQL, a primary key is a constraint used to ensure data uniqueness and integrity. A primary key can be a single field or a combination of multiple fields that uniquely identify records in the table. Adding a primary key typically involves two approaches: defining it when creating the table or adding it to an existing table.

Specifying the Primary Key When Creating the Table

For example, to create a table named students with student_id as the primary key, use the following SQL statement:

sql
CREATE TABLE students (
    student_id INT NOT NULL,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (student_id)
);

In this example, the student_id field is defined as the primary key, meaning that each student's ID in the students table must be unique.

Adding a Primary Key to an Existing Table

If a table has already been created but lacks a primary key, you can use the ALTER TABLE command to add one. Suppose you have a students table but forgot to set student_id as the primary key; use the following SQL statement to modify the table:

sql
ALTER TABLE students
ADD PRIMARY KEY (student_id);

This command sets the student_id field as the primary key.

Important Considerations

• Ensure that the field used as the primary key has no duplicate values.
• The primary key field must not accept NULL values, so the field should be defined as NOT NULL.
• If the table is very large, adding a primary key may take some time because MySQL must verify data uniqueness and create an index.