Introduction to MySQL Primary Key
The following article provides an outline for MySQL Primary Key. When we create the tables to store the data in the database, at that time in order to recognize the row and identify each of the records stored in the table, we use indexes. The primary index is defined on the table by applying the primary key constraint. We can define only one primary key on a single table. The primary key can consist of one or more columns that together provide uniqueness to the row which can further be used for its identification.
Primary Key Concept and Rules
A set of columns or a single column that can help in the identification of each of the row uniquely in the table.
The rules followed by the primary key is as specified below:
- When a single column is used for defining the primary key then it must contain the unique values stored in each of the rows of the table while in case of defining the primary key on multiple columns the combination of the value of all the columns on which the primary key is defined should have unique value together.
- When the primary key is defined on the column then implicitly the NOT NULL constraint is applied for the column or columns on which the primary key constraint is defined. Hence, the NULL value cannot be stored in the columns that define the primary key. If we try inserting or updating such columns with NULL value then MySQL will issue an error.
- Any table in MySQL can have only one or none of the primary key defined on it.
- Most often, the datatype of the column that is used to define the primary key is kept integer because MySQL works very fast with integers. This will lead to faster and quick retrieval of the resultset.
- A table can have only one column on which the AUTO_INCREMENT property can be assigned. Most often the column on which the primary key is defined is kept of integer type and AUTO_INCREMENT property is assigned to it as it will lead to the generation of the values of the column uniquely and sequential fashion.
- Whenever a primary key is defined on the table, MySQL automatically internally creates an index named PRIMARY on the columns on which the primary key is defined.
- While assigning the data type to the column of the primary key, we need to be careful because it should contain all the rows that we wish to insert and hence the sufficient number of keys should be available and the range of the data type of the column needs to be defined accordingly. Most of the time, INT datatype is sufficient but when too many records are to be stored at a large scale then the column of the primary key should be defined as BIGINT data type.
Syntax of MySQL Primary Key
When a primary key needs to defined only on a single column then we can assign the PRIMARY KEY property in front of the column and its data type as its property.
The syntax of defining the primary key on the single column is given below:
CREATE TABLE name_of_table(
primary_col datatype PRIMARY KEY,
...
);
Where primary_col is the name of the column on which primary key is be defined.
When we have to define the primary key on more than one column then we can use the PRIMARY KEY table constraint at the end of the list of columns of the table that are specified in a comma-separated format. Below is the syntax for the same. Note that this syntax can also be used when the key is defined on the single column.
CREATE TABLE name_of_table(
primary_col1 datatype,
primary_col2 datatype,
...,
other_columns datatype,
...,
PRIMARY KEY(list_of_columns)
);
Where primary_col1, primary_col2, and so on are the columns on which the primary key is to be defined and are the same specified again in the list_of_columns parameter to table constraint PRIMARY KEY.
Examples of MySQL Primary Key
Given below are the examples mentioned:
Example #1
Let us create the table named Developer and define the column ID as its primary key using the following query statement at the time of the creation of the table.
Code:
CREATE TABLE `Developer` (
`ID` int(11) PRIMARY KEY,
`developer_name` varchar(30) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Output:
And gives the following output after describing the table showing the primary key constraint on ID column.
Code:
desc Developer;
Output:
Example #2
Let us now create a table named educba_articles on which we will define primary key containing two columns tech_id and writer_id and we will use the following query statement to do so.
Code:
CREATE TABLE `educba_articles` (
` tech_id` int(11) NOT NULL,
` writer_id` int(11) NOT NULL,
` name` varchar(10) NOT NULL,
` author` varchar(10) NOT NULL,
` rate` decimal(5,2) DEFAULT NULL,
` month` varchar(10) NOT NULL,
` status` varchar(10) NOT NULL,
` pages` int(11) DEFAULT NULL,
PRIMARY KEY (` tech_id,writer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Output:
And gives the following output after describing the table showing the primary key constraint on tech_id and writer_id column.
Code:
desc educba_articles;
Output:
ALTER TABLE Command to Add a Primary Key
When an existing table needs to be modified to add the primary key on certain column or columns then we can use ALTER TABLE command.
Syntax:
ALTER TABLE name_of_table
ADD PRIMARY KEY(list_of_columns);
Example:
Let us consider one example, we will create a table named customer without any primary index in it using the following statement.
Code:
CREATE TABLE `customers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`address` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Output:
Now, we will add a primary key on id column using the following query statement.
Code:
ALTER TABLE `customers`
ADD PRIMARY KEY (`id`);
Output:
Let us describe and see the table structure.
Code:
DESC customers;
Output:
Conclusion
We need a primary index on the tables of MySQL to recognize the rows and records of the table uniquely and define the index on which the rows will further be identified. This makes the query execution for data retrieval on this columns faster when the value of primary key columns is mentioned in the where clause of the query.
Recommended Articles
This is a guide to MySQL Primary Key. Here we discuss the primary key concept, rules and ALTER TABLE command to add a primary key with query examples. You may also have a look at the following articles to learn more –
