How to Make a Text Column a Primary Key in Mysql

Back to: MySQL Tutorials for Beginners and Professionals

PRIMARY Key Constraint in MySQL with Examples

In this article, I am going to discuss PRIMARY KEY Constraint in MySQL with Examples. Please read our previous article where we discussed CHECK Constraint in MySQL.

What is Primary Key in MySQL?

The Primary Key in MySQL is the combination of UNIQUE and NOT NULL Constraint.That means it will not allow either NULL or Duplicate values into a column or columns on which the primary key constraint is applied. Using the primary key, we can enforce entity integrity i.e. using the primary key we can uniquely identify each record.

A table should contain only 1 Primary Key which can be either on a single or multiple columns i.e. the composite primary key. The Primary Key constraint in MySQL can be applied to numeric and string data types such as INT, VARCHAR, etc.

Understanding the Primary Key Constraint in MySQL:

In MySQL, a table can have only one primary. Let us prove this. Please try to execute the below SQL Script where we used the Primary key on more than one column (Id and Email).

CREATE TABLE Employee  (      Id 		INT PRIMARY KEY,      Name 	VARCHAR(500),      Email 	VARCHAR(50) PRIMARY KEY,     Department  VARCHAR(50) );          

When you try to execute the above Create Table SQL query, you will get the following error. The error message clearly tells us that we are trying to create multiple primary keys which are not possible.
Error Code: 1068. Multiple primary key defined

To overcome the above error, remove one primary key and execute the CREATE TABLE SQL statement as shown below.

CREATE TABLE Employee  (      Id 		INT PRIMARY KEY,      Name 	VARCHAR(500),      Email 	VARCHAR(50),     Department  VARCHAR(50) );          

Now when you execute the above SQL code, it executed successfully and creates the Employee table. Here, we marked the Id column as the primary key. let us verify the Employee table structure by executing the below SQL Statement.
DESCRIBE Employee;
Once you execute the above SQL Statement, it will give you the below output.

Let's execute the following insert statement to insert a record into the Employee table
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Sambit', 'Sambit@dotnettutorials.net', 'IT');

Once you execute the above INSERT Statement, one record is inserted into the Employee table as expected.

Adding Duplicate value in the Primary Key Column:

Now let us see what happens when we try to insert a duplicate value into the Primary key column i.e. into the Id column. In our Employee table, one record exists with the Id 1. Now. Let us try to insert another employee with the same ID 1. Please try to execute the below insert statement to insert a duplicate record.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'Anurag', 'Anurag@dotnettutorials.net', 'IT');

When we try to execute the above SQL statement, it gives us the below error. That means the Primary Key constraint will not accept duplicate values in it.
Error Code: 1062. Duplicate entry '1' for key 'employee.PRIMARY'

Inserting NULL in Primary Key Column in MySQL:

Now, let us try to insert a NULL value into the primary key column i.e. Id column by executing the below insert statement.
INSERT INTO Employee (Id, Name, Email, Department) VALUES (NULL, 'Anurag', 'Anurag@dotnettutorials.net', 'IT');

When we try to execute the above Insert SQL statement, it gives us the following error which clearly states that the Id value cannot be the null value which proves that Primary Key will not accept NULL.
Error Code: 1048. Column 'Id' cannot be null

Note: So, the Primary key neither accepts NULL Values or Duplicate Values in MySQL.

As we already discussed except the NOT NULL constraint, all other constraints can be imposed either at the Table or Column level. Let us understand the same concept with PRIMARY KEY Constraint.

Imposing PRIMARY KEY Constraint at Column Level:

Let us understand how to Impose PRIMARY KEY Constraint at column level in MySQL with an example. As you can see in the below Create Table statement, we have specified PRIMARY KEY Constraints directly at the column level for the Id Column.

CREATE TABLE Student (      Id      INT PRIMARY KEY,      Name    VARCHAR(50),      Email   VARCHAR(10) );

Imposing PRIMARY KEY Constraint at Table Level:

Let us understand how to impose MySQL PRIMARY KEY constraint at table level with an example. As you can see in the below Create Table statement, first, we created all the three columns and then we created the PRIMARY KEY constraints and while creating the PRIMARY KEY constraint we specified the column name to which the constraint belongs.

CREATE TABLE StudentAddress (      Id      INT,      Name    VARCHAR(50),      Email   VARCHAR(10),     PRIMARY KEY(Id) );

Note: There is no difference in behavior whether we imposed the PRIMARY KEY Constraint at the table level or at the column level but if we imposed the Constraint at the table level, then we have the chance to impose the composite PRIMARY KEY constraints.

Creating PRIMARY KEY Constraint with Custom name in MySQL:

Let us understand how to provide a custom name to the PRIMARY KEY constraint in MySQL. In order to understand this, please execute the below SQL Script which will create the StudentDetails table. If you further notice, here we created the PRIMARY KEY constraints by giving our own names such as ID_PRIMARY.

CREATE TABLE StudentDetails (      Id      INT,      NAME    VARCHAR(30),      Email   VARCHAR(100),     CONSTRAINT ID_PRIMARY PRIMARY KEY (Id) );          

What is the Composite Primary key in MySQL?

It is also possible in MySQL to create the Primary Key constraint on more than one column and when we do so, it is called a Composite Primary Key. It is only possible to impose the Composite Primary Key at the table level, it is not possible at the column level. In a composite primary key in MySQL, each column can accept duplicate values but the combination should not be duplicated. If this is not clear at the moment, then don't worry, we will try to understand this with some examples.

Examples to Understand MySQL Composite Primary Key:

Please have a look at the following create table statement. Here, we impose the Primary Key Constraint at the table level i.e. after all the columns are created. While creating the primary key we are providing two columns i.e. Name and Email. As the Primary Key is created based on more than one column, it is termed as a composite primary key.

CREATE TABLE EmployeeDetails  (      Id  INT,      Name VARCHAR(50),      Email  VARCHAR(50),      PRIMARY KEY(Name, Email)  );          

Once you execute the above SQL Statement, let us verify the EmployeeDetails table structure by executing the below SQL Statement.
DESCRIBE EmployeeDetails;
Once you execute the above SQL Statement, it will give you the below output.

Now, execute the below INSERT SQL Statements.

INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (1, 'Sambit', 'Sambit@dotnettutorials.net');  INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (2, 'Sambit', 'Sambit1@dotnettutorials.net');  INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (3, 'Sambit1', 'Sambit1@dotnettutorials.net');          

Once you execute the above SQL Statements, three records are inserted into the EmployeeDetails table. Here, if you notice. In the second and third SQL Statements, we have some duplicate values. In Statement2, the Name is duplicate and in Statement3, the Email is duplicate. But all these records are inserted, this is because the combination of Name and Email is not duplicated. Now, try to execute the below SQL Statement.

INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (4, 'Sambit', 'Sambit@dotnettutorials.net');

When we try to execute the above INSERT Statement, we will get the below error. This is because the combination of the above Name and Email is already existing in the database.

Error Code: 1062. Duplicate entry 'Sambit-Sambit@dotnettutorials.net' for key 'employeedetails.PRIMARY'

How to add PRIMARY KEY Constraints to the existing table in MySQL?

It is also possible to add PRIMARY KEY Constraints to the existing table in MySQL But the condition is, on the column(s) on which we are going to apply the Primary Key Constraint should not contain any NULL or Duplicate Values else it will not allow you to create the Primary Key Constraint. Let us understand this with an example. First, create the following table without using the Primary Key Constraint.

CREATE TABLE Test (      Id      INT,      NAME    VARCHAR(30),      Email VARCHAR(100) );          

Once you created the Test table, now we want to apply the Primary Key Constraint on the Id column. To do so, please execute the following alter table statement.
ALTER TABLE Test ADD PRIMARY KEY (Id);

Once you execute the above ALTER Statement, it will create the PRIMARY KEY Constraint on the ID column. You can verify the same by executing the below statement.
DESCRIBE Test;

Once you execute the above SQL Statement, it will give you the following output.

How to DELETE PRIMARY Constraints in MySQL?

You can use the following SQL Statement to delete an existing PRIMARY KEY Constraint in MySQL. The following SQL Statement will delete the PRIMARY Constraint from the Id column of the Test table.
ALTER TABLE Test DROP PRIMARY KEY;

Once you execute the above ALTER Statement, it will delete the PRIMARY KEY Constraint from the Test table. You can verify the same by executing the below statement.
DESCRIBE Test;

Once you execute the above SQL Statement, it will give you the following output.

When do we need to choose Primary Key?

When we need the following features on a column, then we need to make that column Primary Key in MySQL

1. NULLs should not be allowed.
2. It should be unique
3. It should not be modified.

Note: The PRIMARY KEY Constraint is useful to restrict storing of duplicate data row values in a given column. The PRIMARY KEY is similar to UNIQUE Constraint but unlike UNIQUE Constraint there can be only one PRIMARY KEY for one table. The PRIMARY KEY automatically sets UNIQUE Constraint for that table column. The PRIMARY KEY column can not contain NULL values. The Primary key can be defined while creating a new database table or can be added by using ALTER TABLE statement.

In the next article, I am going to discuss the Foreign Key Constraint in MySQL with Examples. Here, in this article, I try to explain PRIMARY KEY Constraint in MySQL with Examples and I hope you enjoy this article.

How to Make a Text Column a Primary Key in Mysql

Source: https://dotnettutorials.net/lesson/primary-key-constraint-mysql/

Share this post