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:
We can create a primary key in two ways:
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.
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:
If we want to create more than one primary key column into the table, use the below syntax:
The following table explains the parameters in detail.
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:
Next, use the insert query to store data into a table:
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.
If you want to define the primary key on multiple columns, use the query as below:
In the output, we can see that the primary key value contains two columns that are Student_ID and Roll_No.
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.
Following are the syntax of the ALTER TABLE statement to create a primary key in MySQL:
The following statement creates a table "Persons" that have no primary key column into the table definition.
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.
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:
Primary Key vs. Unique Key
The following comparison chart explains some of the common differences between both of them: