MySQL Primary Key

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format and to uniquely identify each record in a table, we require a Primary Key.

In this article, we will learn how to add, modify, and remove the primary key in MySQL tables with examples.

Primary Key in MySQL

A MySQL Primary Key is a unique column/field in a table that should not contain duplicate or NULL values and is used to identify each record in the table uniquely.

The role of the primary key constraint is to maintain the integrity of the database by preventing duplicate and null values in the key column. A table can only have one primary key, but it can be defined on one or more fields and that is called a composite primary key.

We can use the AUTO_INCREMENT attribute on the primary key field so that a new value is automatically added to the column when we create a new row.

A primary key makes the query execution fast, as MySQL automatically creates a “Primary” index for the primary key.

Rules for Primary Key

• Each value in the primary key column must be unique.
• No Null values are allowed in the primary key column.
• Primary key value should remain constant throughout the lifespan of the record.
• Primary keys can be either system-generated such as auto-incremented integers or natural keys such as existing data that uniquely identifies a record (for example- Email).

How to Add Primary Key in MySQL Table

There are two ways in MySQL to create primary key in a table:

• Using CREATE TABLE Statement
• Using ALTER TABLE Statement

We can either create a primary key while create the table or add primary key after table creation. Let’s look at both these methods with examples:

Using PRIMARY KEY on CREATE TABLE

You can create Primary Key while creating a Table using the CREATE TABLE statement.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (column1)
);
//Composite Primary key
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    PRIMARY KEY (column1, column2)
); 

Example

Query to create a table and add primary key using CREATE TABLE statement:

CREATE TABLE GeeksForGeeks(Id int, Name varchar(20), PRIMARY KEY(Id));

Adding Primary key using CREATE TABLE Statement

Using PRIMARY KEY on ALTER TABLE

You can add a primary key in an already existing table by using the ALTER TABLE statement. If the table has already one primary key then you can’t add another by using the ALTER TABLE statement.

Syntax

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Example

Query to add primary key by using ALTER TABLE statement:

ALTER TABLE GeeksForGeeks ADD PRIMARY KEY(Name);

Adding Primary Key using ALTER TABLE Statement

Drop Primary Key

To drop/remove a primary key from a table you can use the ALTER TABLE statement.

Syntax

ALTER TABLE table_name DROP PRIMARY KEY;

Example

Query to drop primary key by using ALTER TABLE statement:

ALTER TABLE GeeksForGeeks DROP PRIMARY KEY;

Destroying Primary Key

Difference between Primary Key and Unique Key

Primary Key	Unique Key
Each value in the primary key column must be unique.	Each value in the unique key column must be unique, but a table can have multiple unique keys.
The primary key column cannot contain NULL values.	The unique key column allows one NULL value.
A table can have only one primary key.	A table can have more than one unique key.
By default, a primary key creates a clustered index (affects physical storage order of data).	The default index for a unique key is non-clustered.

Conclusion

In conclusion, a primary key play an important role in uniquely identifying and distinguishing individual records within a database. This key attribute ensures data integrity and helps us to establish relationships between different tables.

We have covered the primary key with examples, understanding how to create, add and drop them from a table. Following the given queries, you can practice the examples on your system and learn faster!