MySQL Primary Key

MySQL primary key is a single or combination of the field, which is used to identify each record in a table uniquely. If the column contains primary key constraints, then it cannot be null or empty. A table may have duplicate columns, but it can contain only one primary key. It always contains unique value into a column.

When you insert a new row into the table, the primary key column can also use the AUTO_INCREMENT attribute to generate a sequential number for that row automatically. MySQL automatically creates an index named “Primary” after defining a primary key into the table. Since it has an associated index, we can say that the primary key makes the query performance fast.

Rules for Primary key

Following are the rules for the primary key:

The primary key column value must be unique.
Each table can contain only one primary key.
The primary key column cannot be null or empty.
MySQL does not allow us to insert a new row with the existing primary key.
It is recommended to use INT or BIGINT data type for the primary key column.

We can create a primary key in two ways:

CREATE TABLE Statement
ALTER TABLE Statement

Let us discuss each one in detail.

Primary Key Using CREATE TABLE Statement

In this section, we are going to see how a primary key is created using the CREATE TABLE statement.

Syntax

The following are the syntax used to create a primary key in MySQL.

If we want to create only one primary key column into the table, use the below syntax:

  CREATE TABLE table_name(      col1 datatype PRIMARY KEY,      col2 datatype,      …  );  

If we want to create more than one primary key column into the table, use the below syntax:

  CREATE TABLE table_name  (    col1 col_definition,    col2 col_definition,    …      CONSTRAINT [constraint_name]      PRIMARY KEY (column_name(s))  );  

Parameter Explanation

The following table explains the parameters in detail.

Parameter Name	Descriptions
Table_name	It is the name of the table that we are going to create.
Col1, col2	It is the column names that contain in the table.
Constraint_name	It is the name of the primary key.
Column_name(s)	It is the column name(s) that is going to be a primary key.

Primary Key Example

The following example explains how a primary key used in MySQL.

This statement creates a table named “Login” whose “login_id” column contains the primary key:

  Mysql> CREATE TABLE Login(     login_id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(40),     password VARCHAR(55),     email VARCHAR(55)  );  

Next, use the insert query to store data into a table:

  mysql> INSERT INTO Login(login_id, username, password, email)   VALUES (1,’Stephen’, 15343434532, ‘stephen@tutoraspire.com’),   (2, ‘Joseph’, 35435479495, ‘Joseph@tutoraspire.com’);    mysql> INSERT INTO Login(login_id, username, password, email)   VALUES (1,’Peter’, 15343434532, ‘peter@tutoraspire.com’);  

Output

In the below output, we can see that the first insert query executes successfully. While the second insert statement fails and gives an error that says: Duplicate entry for the primary key column.

MySQL Primary Key

If you want to define the primary key on multiple columns, use the query as below:

  mysql> CREATE TABLE Students (  Student_ID int,   Roll_No int,  Name varchar(45) NOT NULL,   Age int,   City varchar(25),  Primary Key(Student_ID, Roll_No)  );  

In the output, we can see that the primary key value contains two columns that are Student_ID and Roll_No.

MySQL Primary Key

Primary Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. When the table does not have a primary key, this statement is used to add the primary key to the column of an existing table.

Syntax

Following are the syntax of the ALTER TABLE statement to create a primary key in MySQL:

Example

The following statement creates a table “Persons” that have no primary key column into the table definition.

  mysql> CREATE TABLE Persons (  Person_ID int NOT NULL,   Name varchar(45),   Age int,   City varchar(25)  );  

After creating a table, if we want to add a primary key to this table, we need to execute the ALTER TABLE statement as below:

We can see the output where both statements executed successfully.

MySQL Primary Key

If the table needs to add the primary key into a table that already has data into the column, then it must be sure to the column does not contains duplicates or null values.

DROP Primary Key

The ALTER TABLE statement also allows us to drop the primary key from the table. The following syntax is used to drop the primary key:

Example

Primary Key vs. Unique Key

The following comparison chart explains some of the common differences between both of them:

SN	Primary Key	Unique Key
1.	It is a single or combination of the field, which is used to identify each record in a table uniquely.	It also determines each row of the table uniquely in the absence of a primary key.
2.	It does not allow to store a NULL value into the primary key column.	It can accept only one NULL value into the unique key column.
3.	A table can have only one primary key.	A table can have more than one unique key.
4.	It creates a clustered index.	It creates a non-clustered index.

Next TopicMySQL Foreign Key

MySQL Primary Key

MySQL Primary Key

Rules for Primary key

Primary Key Using CREATE TABLE Statement

Parameter Explanation

Primary Key Example

Primary Key Using ALTER TABLE Statement

Example

DROP Primary Key

Primary Key vs. Unique Key

MySQL Import CSV File in Database/Table

MySQL regexp_like() function

You may also like