How to Add the Primary Key to a MySQL Table

1. Add the Primary Key to a MySQL Table
2. Use the CREATE TABLE to Add Primary Key in MySQL
3. Use the ALTER TABLE to Add Primary Key in MySQL

This article presents how to add the primary key to a MySQL table using the CREATE TABLE and ALTER TABLE statements.

Add the Primary Key to a MySQL Table

It is important to keep the following rules before adding the primary key.

1. The primary key field values must be unique.
2. The column used for creating a primary key cannot be empty or NULL.
3. MySQL Server does not insert a new record with an already existing primary key.
4. Only one primary key is allowed in a table.

We can create a primary key using the CREATE TABLE statement or ALTER TABLE statement. Let’s see both below.

Use the CREATE TABLE to Add Primary Key in MySQL

Typically, we create the primary key while creating a table. The following approach is preferred if the primary key has a single column.

We use the primary key constraint as the field (column) constraint.

Example Code:

CREATE TABLE students(
	ID INT NOT NULL PRIMARY KEY,
    STUDENT_NAME VARCHAR(50) NOT NULL
);

We use the following statement to ensure that the primary key is created.

DESCRIBE students;
# Alternatively, we can write as given below
DESC students;

Output:

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ID           | int         | NO   | PRI | NULL    |       |
| STUDENT_NAME | varchar(50) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

In the above output, the PRI under the Key column shows that the respective value in the Field column is used for creating a primary key.

Suppose we want to create a primary key by using multiple columns. Then, we can do that as given below.

Example Code:

CREATE TABLE courses(
	COURSE_ID INT NOT NULL,
    STUDENT_ID INT NOT NULL,
    COURSE_NAME VARCHAR(50) NOT NULL,
    CONSTRAINT PK_STUDENT_COURSE
   	PRIMARY KEY (COURSE_ID,STUDENT_ID)
);

Use the DESCRIBE statement to check whether the primary key is created.

DESCRIBE courses

Output:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| COURSE_ID   | int         | NO   | PRI | NULL    |       |
| STUDENT_ID  | int         | NO   | PRI | NULL    |       |
| COURSE_NAME | varchar(50) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.43 sec)

We see two PRI values in the Key column, which means the COURSE_ID and STUDENT_ID are used as the primary key.

Remember, we must use a primary key constraint as a table (relation) constraint when it involves multiple columns separated by comma (,), but it can also be done with a single column. See the following query.

Example Code:

CREATE TABLE students(
	ID INT NOT NULL,
    STUDENT_NAME VARCHAR(50) NOT NULL,
    PRIMARY KEY(ID)
);

Notice that the primary key has only one column, but we use the primary key constraint as a table constraint. You can find the difference between table level and column level constraints here.

Use the ALTER TABLE to Add Primary Key in MySQL

We realize after creating a table that a particular column within a table must be a primary key to avoid redundancy. In that case, we use the ALTER TABLE statement.

When a table does not contain a primary key, this statement lets us modify and create a primary key to an existing table’s column(s). Suppose we have a person table with ID and FULLNAME as columns.

Example Code:

# Create a table without a primary key
CREATE TABLE person(
	ID INT NOT NULL,
    FULLNAME VARCHAR(50) NOT NULL
);

# Describe `person` table
DESCRIBE person;

Output:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int         | NO   |     | NULL    |       |
| FULLNAME | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

See, we don’t have any primary key in the above output. Now, we can use the ALTER TABLE statement as given below to create a primary key on the desired column.

Example Code:

ALTER TABLE person ADD PRIMARY KEY(ID);

Remember, the field used for creating a primary key must have unique values and do not contain NULL or empty. Now, use the DESCRIBE statement to ensure that a primary key has been created successfully.

Example Code:

DESCRIBE person;

Output:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int         | NO   | PRI | NULL    |       |
| FULLNAME | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)