intro
A MySQL primary key is a specific type of B-tree index that helps further your database performance. Here’s what they are, how they work, and when to use them.
If you are a frequent reader of The Table, you will surely know your way around indexing. Various database management systems offer you multiple ways to index data, and MySQL and its brothers MariaDB and Percona Server are no exception. Primary keys are one of those ways!
Time to dig into the MySQL primary key mechanism.
What is a MySQL PRIMARY KEY Index?
Contrary to popular belief, MySQL PRIMARY KEY indexes aren’t a different index type available in MySQL. These indexes are B-tree indexes with a specific function to ensure that our column doesn’t contain duplicate rows and, if necessary, to increment values automatically. For more information, read our guide on how to update a value incrementally in MySQL.
How To Define a PRIMARY KEY Constraint in MySQL
PRIMARY KEY indexes are often defined when defining a table and in that case, they look like so:
1
CREATE TABLE demo_table (
2
`id` INT PRIMARY KEY,
3
`message` VARCHAR(120) NOT NULL DEFAULT '',
4
`timestamp` TIMESTAMP NOT NULL,
5
);
When creating tables, primary keys can also be defined using CONSTRAINT like so:
1
CREATE TABLE `demo_table` (
2
`id` INT NOT NULL,
3
`message` VARCHAR(120) NOT NULL DEFAULT '',
4
`timestamp` TIMESTAMP NOT NULL,
5
CONSTRAINT `pk_idx` PRIMARY KEY(`id`)
6
);
However, note that when creating primary key constraints on tables upon their creation you may see a warning from MySQL or MariaDB saying that the title of the index was ignored. That happens because all primary key indexes that are created by the user will have a title of PRIMARY.
Such a title uniquely identifies a primary key index, and since a primary index can only manifest itself once (it’s impossible to have multiple primary keys on a single table), the title isn’t an issue:
ALTER TABLE statements would also work for defining a PRIMARY KEY MySQL constraint:
1
ALTER TABLE `demo_table` ADD PRIMARY KEY(`id`);
Here, it would also be wise to note that most primary key indexes are defined on id columns. In the real world, you will rarely see a primary key that is defined on something else and that has a very good reason — primary keys often support automatically incrementing columns and the only values that are accepted for the column are NULL values. Take a look at the examples below:
In other words, our database is telling us that if we want to have a primary key, we better:
With requirements like these, it’s easy to see why primary keys are the primary choice for use cases that necessitate automatically incrementing columns.
Rules of Primary Key MySQL Indexes
Requirements like the ones above pave the way for a couple of rules situated around PRIMARY KEY MySQL indexes:
Keeping the MySQL PRIMARY KEY rules in mind will ensure that your way through indexing in MySQL remains smooth — however, do remember that as primary key indexes are usually defined on id columns, not all queries may make use of them and if you want to see positive results of those indexes in action, you need to craft queries in a relevant manner, too.
Making Use of the MySQL Primary Key
To make use of the MySQL primary key present in your tables, your database must include the column (or columns) based on the primary key in its SELECT clause.
Not everything’s black and white either — there are a couple of rules you will need to adhere to and I’ll get into those after providing you with some examples. In DbVisualizer, we’ll run these queries:
Our primary key resides on the id column, so the results are as follows:
From the output, we can see that in our case, MariaDB acted in a variety of ways:
That happened because our queries were crafted in a certain way that allowed certain indexes to be used and disallowed the others — in other words, if you want MySQL (or any other database management system, for that matter) to make use of indexes you’ve defined, make sure to define the column(s) you’ve indexed after the WHERE clause.
If you find yourself defining multicolumn PRIMARY KEY indexes, keep in mind that MySQL can read multicolumn indexes forward (from the left side to the right) or backward (from the right to the left), but not the other way around. That means that if your primary key is residing on two columns id and customer_id, for your query to make use of the index it would have to make use of the id or customer_id, id and customer_id, or the other way around. Thus, be careful when defining indexes and always make use of clauses like EXPLAIN to find out why they work in ways they do!
All kinds of indexes are useful — some may be more useful than others, but the effectiveness of indexes is hard to deny. Use the EXPLAIN clause in front of your queries to figure out whether they’re using indexes and what kind of indexes are in use and your database will take you far using primary keys alone.
Making Use of DbVisualizer
After you find yourself making good use of indexing practices, also don’t forget SQL clients. DbVisualizer is the highest-rated SQL client on the market and that’s because of a good reason: the SQL client supports many database management systems and has the highest user satisfaction in the market.
DbVisualizer is known for helping you visualize the data within your database and the queries related to it: its powerful ERD-generation capabilities will help you better understand your data, and once you do so, it will also let you execute SQL queries with ease by acting as a top-notch SQL editor that’s able to format your queries, build them in a visual manner, and automatically complete parts of them so you don’t have to.
To dive into the ERD schema generation capabilities of DbVisualizer, expand any database, navigate towards Tables, then navigate towards the References tab:
Then, once you’re there, you will be able to observe “what connects to what” in the form of ERD schemas — isn’t that cool?
Aside from ERD schemas and a powerful SQL query editor, DbVisualizer has many miscellaneous features you and your team will find useful. Its grid coloring capabilities are just one of them:
DbVisualizer has many other features not mentioned here, too — that includes the ability to add permissions for the execution of certain statements, the ability to export data sets in a wide variety of data formats, and much more. Make use of DbVisualizer today and let us know what you think!
Summary
MySQL PRIMARY KEY indexes are B-tree indexes with a caveat: one table can only have one primary key, and most of the columns that have primary key indexes on them will also be defined as automatically incrementing id columns as well.
That’s not to say that PRIMARY KEY MySQL indexes shouldn’t be used: they can be defined as standalone indexes or as multicolumn indexes and as long as you know their upsides and downsides, you should be good to go.
We hope that this blog has been helpful to you and that you’ve learned something new, and until next time.
Frequently Asked Questions
What is a MySQL PRIMARY KEY?
In MySQL, a PRIMARY KEY is a type of B-tree index that uniquely identifies rows in a table. One table can have only one primary key, but primary key indexes can be defined on one or more columns in one go.
Do all tables have a primary key Index?
Yes — due to concepts such as Generated Invisible Primary Keys (GIPK), all tables have primary keys, even if they’re not visible from the outset. They can be enabled by running a query like SET sql_generate_invisible_primary_key=ON and observed by running a SHOW CREATE TABLE statement.
Why should I use DbVisualizer?
Consider using DbVisualizer because DbVisualizer is the highest-rated SQL client in the market packed with goodies such as its SQL editor, ERD schema generation capabilities, data visualization, ability to export data in various formats, and so much more.
Where can I learn more about primary keys and other database secrets?
To learn more about concepts surrounding indexes, primary keys, and the like, consider subscribing to a YouTube channel Database Dive — attend workshops, seminars, conferences and gatherings, and, of course, come back to The Table: our blog about database performance, availability, security, and other topics very frequently.
