MySQL Primary Key

Last Updated: 27 July, 2023

In MySQL, a primary key is a column or a set of columns that uniquely identify each row (record) in a table. It serves as a unique identifier and enforces data integrity by ensuring that no two rows in the table have the same primary key value. Having a primary key is essential for most database tables, as it allows for efficient searching, indexing, and maintaining relationships between different tables.

A table can have only one primary key.

To define a primary key in MySQL, we can use the PRIMARY KEY constraint.

There are two ways to add the primary key to a table:

1. At the time of creating a new table

2. By altering an existing table

Let's understand it through the syntax and examples given below.

1. Add Primary Key during table creation

Generally, we add a primary key at the time of table creation, which is a very simple way to add a primary key to a table.

 CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype,
  ...
 );

Here's an example of creating a table with a primary key:

 CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(100)
 );

In this example, the employee_id column is designated as the primary key, ensuring that each employee's ID is unique within the table.

2. Add Primary Key by altering an existing table

We can also define the PRIMARY KEY constraint separately after creating the table, so let's first create a table and then add a primary key.

  CREATE TABLE customer (
  cust_id INT,
  name VARCHAR(50),
  age INT
  );

Adding a primary key using ALTER TABLE statement.

  ALTER TABLE customer ADD PRIMARY KEY (cust_id);

In this example, the cust_id column is defined separately as the primary key using the ALTER TABLE statement.

DROP Primary Key

To drop a primary key from a table, we use the ALTER TABLE statement in MySQL.

The syntax to drop a primary key in MySQL is:

  ALTER TABLE table_name DROP PRIMARY KEY;

Suppose we have a table called customer and it has a primary key. Let's see in the below query how the primary key is being removed from the table.

  ALTER TABLE table_name DROP PRIMARY KEY;

In this example, we've dropped the primary key from the customer table. We do not need to specify the name of the primary key.

---

Keep in mind that each table can have only one primary key, and it should consist of non-null and unique values. If our data model requires a combination of columns to form a unique identifier, we can use a composite primary key by specifying multiple columns in the PRIMARY KEY constraint.