Summary: in this tutorial, you’ll learn about the primary key and how to add it to a table using the MySQL Primary Key constraint.
Introduction to MySQL Primary Key
Columns and rows make up a table. A table usually has a column (or a collection of columns) whose values uniquely identify each row in a table. This column or the set of columns is called the primary key.
A table can only have ONE primary key, and this primary key can be made up of one or more columns (fields).
The primary key that consists of two or more columns is also known as the composite primary key.
Primary keys cannot have NULL values and must have UNIQUE values.
Example of one value as primary key in a table
See the following employee table:
| emp_id | emp_name |
| E001 | Paul |
| E002 | Shreeyam |
| E003 | Peter |
| E004 | Vibin |
| E005 | Kabir |
Because the values in the emp_id column uniquely identifies the rows in the employee table, therefore the emp_id column is the primary key of the employee table.
Values may be duplicated in one column if the primary key has two or more columns, but the combination of values from all columns in the primary key must be unique.
Example of a combination of values as primary key in a table
See the following course table.
| course_id | student_id | fees |
| C001 | 1 | 500 |
| C001 | 2 | 500 |
| C001 | 3 | 900 |
| C001 | 4 | 300 |
| C002 | 5 | 500 |
The primary key of the course table consists of the course_id and student_id columns. The values in the course_id column are duplicated. However, the combination of values in the student_id and course_id columns is unique.
We can create the primary key in two ways.
- Using CREATE TABLE statement
- Using ALTER TABLE statement
Adding primary Key with CREATE TABLE Statement
In most cases, the primary key is specified when the table is created.
You can utilize the PRIMARY KEY constraint as a column or table constraint if the primary key is just one column.
If the primary key has two or more columns, the PRIMARY KEY constraint must be used as the table constraint.
Syntax
The syntax for creating a primary key with the CREATE TABLE option is as follows.
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype [ NULL | NOT NULL ], ... );
If you are creating a primary key on more than one column, use the below syntax.
CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n) );
Parameters Explanation
| Parameter Name | Explanation |
| Table_name | The name of the table on that you want to create the primary key |
| column1, column2 | Column names contained in the table |
| Constraint_name | Name of the primary key |
| pk_col1, pk_col2 | Name f the columns on that primary key created |
Example
The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:
CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY, FirstName varchar(255), Age int );
You can also create the primary key with the help of table constraints. The following statement is equivalent to the above.
CREATE TABLE Persons ( ID int NOT NULL, FirstName varchar(255), Age int, CONSTRAINT pk_id PRIMARY KEY (ID) );
The following example creates a primary key on two columns. Here, the columns ID & FirstName are created as the primary key.
CREATE TABLE Persons ( ID int NOT NULL, FirstName varchar(255), Age int, CONSTRAINT pk_id PRIMARY KEY (ID, FirstName) );
Adding primary key with ALTER TABLE statement
You can also add a primary key to an existing table with the help of ALTER TABLE statement.
Syntax
For adding a primary key to the existing table, you use the following syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_value);
Here, CONSTRAINT and constraint_name are optional parameters. You can omit these while adding the primary key.
Example
First, you may use the CREATE TABLE command to create a table without a primary key, although this is not recommended. The ALTER TABLE command is then used to add the primary key to the table.
For example, the following statement creates the Person table without the primary key.
CREATE TABLE Person (
ID int NOT NULL,
FirstName varchar(255),
LastName varchar(255),
Age int
);
Now, you can use the following ALTER TABLE statement to promote the ID column as the primary key.
Example
ALTER TABLE Persons ADD CONSTRAINT pk_id PRIMARY KEY (ID);
You can also omit the CONSTRAINT and constraint_name while adding a primary key.
ALTER TABLE Persons ADD PRIMARY KEY (ID);
Whenever you are adding the primary key into a table that already has data into it, it must be ensured that the column does not contain any duplicate or null values.
Removing the primary key constraint
The primary key of a table is rarely removed. If you really want to do it, use the ALTER TABLE command as follows:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
You may also use the following simpler syntax.
ALTER TABLE table_name DROP PRIMARY KEY;
Example
ALTER TABLE Person DROP CONSTRAINT pk_id;
OR
ALTER TABLE Person DROP PRIMARY KEY;
In this tutorial, you have learned all about the primary key concepts, including how to add and remove the primary key.