Updated May 26, 2023
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 to recognize the row and identify each of the records stored in the table, we use indexes. The table defines the primary index 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 provide uniqueness to the row, which can further be used for identification.
Primary Key Concept and Rules
A set of columns or a single column can help identify each row uniquely in the table.
The rules followed by the primary key are as specified below:
- When defining the primary key using a single column, it must store unique values in each table row. Conversely, when defining the primary key on multiple columns, the combination of values from all the columns used in the primary key should have unique values collectively.
- When you define the primary key on a column, the system implicitly applies the NOT NULL constraint to the column or columns on which the primary key constraint is defined. Therefore, the columns that define the primary key cannot store NULL values. If we try inserting or updating such columns with a NULL value, MySQL will issue an error.
- Any table in MySQL can have only one or none of the primary keys defined on it.
- The column’s datatype used to define the primary key is often kept integer because MySQL works very fast with integers. This will lead to faster and quick retrieval of the resultset.
- A table can only have one column assigned with the AUTO_INCREMENT property. The primary key column is typically kept as an integer type, and the AUTO_INCREMENT property is assigned to it. This ensures that the column values are generated uniquely and sequentially.
- 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; hence a sufficient number of keys should be available, and the range of the data type of the column needs to be defined accordingly. In most cases, the INT datatype is sufficient for determining the primary key column. However, when dealing with large-scale storage of many records, it is advisable to define the primary key column as the BIGINT data type.
Syntax of MySQL Primary Key
When defining a primary key on a single column, we can assign the PRIMARY KEY property to the column and specify its data type as its attribute.
Below is the syntax for defining the primary key on a single column:
CREATE TABLE name_of_table(
primary_col datatype PRIMARY KEY,
...
);Where primary_col represents the column’s name on which the primary key is defined.
When we have to define the primary key on more than one column, we can use the PRIMARY KEY table constraint at the end of the list of columns specified in a comma-separated format. Below is the syntax for the same. Please note that this syntax is applicable even when defining the key on a single column.
CREATE TABLE name_of_table(
primary_col1 datatype,
primary_col2 datatype,
...,
other_columns datatype,
...,
PRIMARY KEY(list_of_columns)
);When defining the table constraint PRIMARY KEY, you should specify the columns on which the primary key is to be defined, such as primary_col1, primary_col2, and so on, in the list_of_columns parameter.
Examples of MySQL Primary Key
Here are the examples:
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 the ID column.
Code:
desc Developer;Output:
Example #2
Let us now create a table named educba_articles on which we will define the primary key containing two columns, tech_id and writer_id, and we will use the following query statement.
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 the tech_id and writer_id columns.
Code:
desc educba_articles;Output:
Add a Primary Key Using the ALTER TABLE Command
To add a primary key on specific columns for an existing table, we can utilize the 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 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:
We will add a primary key on the 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. Mentioning the values of primary key columns in the WHERE clause of a query makes the data retrieval execution faster.
Recommended Articles
We hope that this EDUCBA information on “MySQL Primary Key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
