If you’re a developer who had interactions with indexes in the past, you certainly know that indexes in MySQL come in a variety of shapes and sizes. Simple Talk readers will certainly not be alien to the nuances of indexes within MySQL – as we’ve already covered B-tree indexes in MySQL, and some readers may even be aware of the powers granted to them by database management systems such as PostgreSQL, as it’s known to be one of the most prolific database management systems for indexes and data types alike.

While most of you may be aware of the power that B-tree indexes help you unleash, you may not be aware of specific types of indexes that exist but often remain behind the spotlight – these are PRIMARY KEY indexes.

What are PRIMARY KEY Indexes?

Beneath the hood, PRIMARY KEY indexes are still B-tree indexes in InnoDB, but they are subject to stricter constraints (NOT NULL, UNIQUE). They are essential to the table structure and are used constantly in foreign key relationships, clustering, and lookups.

The purpose of a primary key index is to uniquely identify rows in a table thus, they serve a special purpose. In InnoDB, it physically orders the table data by the primary key (clustered index).

In MySQL, one can add primary key indexes to a column when creating a table like so:

Or when modifying a table like so:

DBAs/developers don’t usually refer to primary keys as “primary key indexes” – a primary key is always an index, but most of the industry folks refer to primary keys as primary keys – not as primary index keys or primary indexes. Just primary keys.

Rules of PRIMARY KEY Indexes

A PRIMARY KEY in MySQL is a type of B-tree index that uniquely identifies each row in a table. However, unlike regular indexes, a primary key comes with special constraints and behaviors:

Working with PRIMARY KEYs

If there are no natural combinations of column values in the table that are unique, PRIMARY KEYs can be assigned that attributes that make them increment automatically and PKs having such attributes will be defined like so:

Working with data inside of such columns isn’t hard – if we insert data, we need to insert NULL values into the column with a PK, or omit the PK column in the insert statement. This causes MySQL to insert the next available value from the auto-increment sequence.

Although inserting NULL will trigger AUTO_INCREMENT, it’s usually safer and clearer to omit the column entirely from your INSERT statement. Use NULL only if you’re relying on a feature like LAST_INSERT_ID(). Otherwise, you risk hitting errors like Duplicate entry ‘0’ for key 'PRIMARY', especially if a row with id = 0 was inserted before AUTO_INCREMENT was added. In such cases, MySQL may start the sequence at 1, but still encounter a conflict with the existing value. This is particularly common if the sql_mode setting treats 0 as a special value.

To solve this problem, we’ll have to define an auto_increment value and try again. This can be done through phpMyAdmin:

Or running a query like so:

Without moving far from here, I should also state that errors like the one related to duplicate entries in the third image can be avoided altogether if we specify an IGNORE clause in our SQL statement. The same statement written like so would ignore errors related to primary keys and insert the data regardless, which can be very useful if you need to insert the data for parsing or related operations:

However, IGNORE is not the cure to all of your problems. When running ALTER queries with auto_increment, you may also face errors such as:

This essentially says that one row already contains a value of “0”. This error typically arises because a value such as 0 or 1 was manually inserted before the AUTO_INCREMENT attribute was added. When MySQL attempts to re-sequence the column, it tries to begin from the lowest unused value — often 1 — and clashes with an existing row.

The fix is usually straightforward: delete or update the conflicting row, or explicitly set the starting point for auto-increment with a statement like ALTER TABLE demo_table AUTO_INCREMENT = 100. If the conflict involves 0, and it’s being treated as a request for an auto-generated value, setting sql_mode = 'NO_AUTO_VALUE_ON_ZERO' will restore clarity by ensuring that 0 is treated as a literal value.

To avoid this sort of error, run a query like SET SESSION sql_mode=' NO_AUTO_VALUE_ON_ZERO':

Using Primary Keys

Let’s now take a look into cases necessitating the usage of our primary keys:

To explain the thinking of our database in operations involving primary keys, we run an ordinary SQL query and add EXPLAIN in front of it. The way EXPLAIN works in different relational database management systems differs from DBMS to DBMS, but in a nutshell, it tells us how and why our query is executed in the way it is. EXPLAIN lets us in on multiple things:

After we understand the internals of our queries, we can see that operations with PK indexes seem to be similar to operations with ordinary B-tree indexes. At the same time,, we should keep in mind that in InnoDB, the primary key is the clustered index — it defines the physical order of the rows. This makes its use significantly different from a secondary index, which contains a copy of the key and a pointer to the clustered index.

Strings as Primary Keys

While the use of string values may have a slight performance cost, natural keys, which are often strings, are perfectly valid, and more maintainable than surrogate keys. Their great advantage is that rows that are referenced by their primary keys are retrieved extremely efficiently. However, it isn’t a good practice to store GUIDs as string values for primary keys. How is that value generated and how do you ensure that it will always be unique? And, if the need arises, how would you compare and search for data?

Given the fact that many universally unique identifiers look like 550e8400-e29b-41d4-a716-446655440000, why do you need a GUID rather than an integer to act as a primary key in the first place? This comes at a cost: larger keys mean more storage, slower joins, and heavier indexes.

Optimizing Operations with Primary Keys

Primary keys aren’t just about enforcing uniqueness — they’re a performance lever. In InnoDB, the table is physically ordered by the primary key (a clustered index), which means smart primary key design can make lookups, joins and range queries lightning fast. But it cuts both ways: a poorly chosen key can drag everything down.

A few golden rules:

Treat your primary key like the backbone of your table — strong, lightweight, and unchanging.

Since primary keys in InnoDB are B-tree indexes, most of the same performance advice applies, so I’d advise you to also apply advice relevant to B-tree indexes.

Generated Invisible Primary Keys (GIPKs)

In the PRIMARY KEY world, some of you may come across terms such as GIPK – as briefly mentioned earlier, such a term stands for a Generated Invisible Primary Key. If a table doesn’t have any defined primary keys and it has no unique indexes with column values defined as NOT NULL, it has a GIPK.

Generated Invisible Primary Keys have a couple of limitations unique to themselves:

By default, the InnoDB storage engine will always create a hidden Primary Key if one isn’t provided (see explanations above), but by providing this feature, MySQL aims to make primary keys visible and usable to the end user. The downside of GIPK is that they’re only available in newer versions of MySQL – they’ve been deployed in MySQL 8.0.30 and act as special kinds of invisible columns. Users of MySQL can get them to work by setting the sql_generate_invisible_primary_key variable to ON, but MariaDB won’t find this value at the helm:

Regardless, users of MySQL can make use of Generated Invisible Primary Keys like so:

You will see something like so:

The my_row_id column has an invisible auto-incrementing primary key! That also means three things for you as a developer/DBA:

All this also means that if you insert rows into your table, you will finally be able to select the My_row_id column and see all of the applicable values:

Keep in mind that as the column is invisible, you are unlikely to see the column in a result set if you use SELECT * FROM `your_table` or similar queries.

More information on Generated Invisible Primary Keys can be found in the MySQL documentation.

Summary

Primary keys are at the heart of relational database design. In MySQL, especially with the InnoDB engine, they’re not just identifiers – they define how your data is stored, retrieved, and related. A well-chosen primary key improves performance, ensures data integrity, and forms the basis for efficient joins and lookups.

While AUTO_INCREMENT integers are popular, MySQL supports a wide range of data types for primary keys, including strings – provided they’re unique, non-null, and thoughtfully chosen. One table, one primary key – but that key can span multiple columns if needed.

And if you forget to define a primary key? MySQL 8.0.30 or later may create one automatically behind the scenes, using a GIPK – helpful, but worth understanding.

Choose wisely. Your future queries will thank you.

PRIMARY KEY Indexes – Frequently Asked Questions

Q: What is a primary key?
A: A primary key is a column or set of columns that uniquely identify each row in a table. It must be unique and not null, and it’s backed by an index.

Q: Can a table have more than one primary key?
A: No. A table can only have one primary key constraint – but it can involve multiple columns. A primary key that involves multiple columns is called a composite primary key.

Q: Can primary keys contain NULL values?
A: No. By definition, primary keys are always NOT NULL. MySQL enforces this even if you forget to declare it.

Q: Do primary keys have to be integers?
A: Not at all. MySQL allows CHAR, VARCHAR, and other data types. Use what best fits your data – just keep it unique and stable.

Q: What’s the deal with Generated Invisible Primary Keys (GIPKs)?
A: In MySQL 8.030+, if no primary or unique key is defined, MySQL may silently create an invisible BIGINT primary key (my_row_id) to ensure every row has a unique identifier. This helps replication and storage, but it’s best not to rely on it unless you understand the trade-offs.

Q: How do primary key indexes help performance?
A: In InnoDB, the primary key determines the physical row order. That means faster lookups, efficient joins, and good use of caching – especially when your queries filter or join on the PK.

Appendix – Table Structure & Data

The example data show in this article was generated using Mockaroo. The table structure used for most queries looked like this:

Note: While the column is called timestamp, the sample values were simply time-like strings rather than precise SQL timestamps. Always check and validate generated data types when mocking tables.