{"id":107444,"date":"2025-08-15T11:00:00","date_gmt":"2025-08-15T11:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107444"},"modified":"2025-08-06T14:55:21","modified_gmt":"2025-08-06T14:55:21","slug":"mysql-index-overviews-primary-key-indexes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-index-overviews-primary-key-indexes\/","title":{"rendered":"MySQL Index Overviews: PRIMARY KEY Indexes"},"content":{"rendered":"\n

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

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 \u2013 these are PRIMARY KEY<\/code> indexes.<\/p>\n\n\n\n

What are PRIMARY KEY Indexes?<\/h2>\n\n\n\n

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

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).<\/p>\n\n\n\n

In MySQL, one can add primary key indexes to a column when creating a table like so:<\/p>\n\n\n\n

CREATE TABLE `demo_table` (\n`id` INT PRIMARY KEY,\n`message` VARCHAR(120) NOT NULL DEFAULT \u2018\u2019,\n`sender` VARCHAR(120) NOT NULL DEFAULT \u2018\u2019,\n`receiver` VARCHAR(120) NOT NULL DEFAULT \u2018\u2019,\n`timestamp` TIMESTAMP NOT NULL,\n\u2026\n);\n<\/pre><\/div>\n\n\n\n
Or when modifying a table like so:<\/p>\n\n\n\n
ALTER TABLE `demo_table` ADD PRIMARY KEY(`id`);<\/pre><\/div>\n\n\n\n
DBAs\/developers don\u2019t usually refer to primary keys as \u201cprimary key indexes\u201d \u2013 a primary key is always an index, but most of the industry folks refer to primary keys as primary keys \u2013 not as primary index keys or primary indexes. Just primary keys.<\/p>\n\n\n\n
Rules of PRIMARY KEY Indexes<\/h2>\n\n\n\n
A PRIMARY KEY<\/code> 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:<\/p>\n\n\n
\n
    \n
  1. A PRIMARY KEY<\/code> column cannot contain NULL<\/code> values<\/strong>. 
    By definition, a primary key must be NOT NULL<\/code> and UNIQUE<\/code>. MySQL enforces this automatically, even if you don’t explicitly declare the column as NOT NULL<\/code>. Any attempt to insert a NULL<\/code> value into a primary key column will result in an error like so:

    ERROR 1048 (23000): Column \u2018colname\u2019 cannot be null<\/pre>
    <\/li>\n\n\n\n
  2. AUTO_INCREMENT<\/code> allows you to omit the value for the column by specifying NULL<\/code>\u2014but does not store NULL<\/code> values.<\/strong>
    When a column is defined with AUTO_INCREMENT<\/code>, you can insert a row without providing a value for that column. If you explicitly insert NULL<\/code>, MySQL treats it as a signal to generate the next sequence value. However, the stored value will never be NULL<\/code>\u2014it will be a generated number. This piece of SQL query would insert a NULL<\/code> value into the id column of a table named demo:

    INSERT INTO `demo` (`id`) VALUES (NULL);<\/pre>
    <\/li>\n\n\n\n
  3. PRIMARY KEYS<\/code> are often, but not always, integers. <\/strong>
    While it’s common to use an integer column with AUTO_INCREMENT<\/code> as a primary key, MySQL supports other data types too. For example, you can define a VARCHAR<\/a> or even a CHAR<\/code> column as a primary key. The key requirement is that the values be unique and not NULL<\/code>.
    <\/li>\n\n\n\n
  4. A table can have only one PRIMARY KEY<\/code>. <\/strong>
    This is a rule of the relational model. A primary key represents the unique identifier for each row, and allowing more than one would violate that principle. However, other uniqueness constraints can be defined using UNIQUE<\/code> indexes.
    <\/li>\n\n\n\n
  5. A <\/strong>PRIMARY KEY<\/b><\/code> can span multiple columns.<\/b><\/span> 
    MySQL supports composite primary keys, where two or more columns together form a unique identifier. In such cases, no individual column is necessarily unique on its own, but the combination must be.
    <\/li>\n\n\n\n
  6. MySQL will create a primary key automatically in some cases.
    <\/strong>Starting with MySQL 8.0.30, if you don\u2019t define any primary or unique key, MySQL may generate a     Generated Invisible Primary Key (GIPK)<\/a> behind the scenes. These internal keys aren’t visible in the table definition, but they serve the purpose of uniquely identifying each row and can be referenced programmatically.<\/li>\n<\/ol>\n<\/div>\n\n\n
    \"\"
    Image 1 – VARCHAR values as PRIMARY KEYs<\/em><\/figcaption><\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    \"\"
    Image 2 – AUTO_INCREMENT for VARCHAR values in MySQL<\/em><\/figcaption><\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Working with PRIMARY KEYs<\/h2>\n\n\n\n
    If there are no natural combinations of column values in the table that are unique, PRIMARY KEYs<\/code> can be assigned that attributes that make them increment automatically and PKs having such attributes will be defined like so:<\/p>\n\n\n\n
    `column_title` INT PRIMARY KEY AUTO_INCREMENT<\/pre><\/div>\n\n\n\n
    Working with data inside of such columns isn\u2019t hard \u2013 if we insert data, we need to insert NULL<\/code> 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.<\/p>\n\n\n\n
    Although inserting NULL<\/code> will trigger AUTO_INCREMENT<\/code>, it’s usually safer and clearer to omit the column entirely from your INSERT<\/code> statement. Use NULL<\/code> only if you’re relying on a feature like LAST_INSERT_ID()<\/code>. Otherwise, you risk hitting errors like Duplicate entry ‘0’ for key 'PRIMARY'<\/code>, especially if a row with id = 0 was inserted before AUTO_INCREMENT<\/code> 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.<\/a><\/p>\n\n\n\n
    \"\"
    Image 3 – Duplicate entry ‘0’ for key ‘PRIMARY’<\/em><\/figcaption><\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To solve this problem, we\u2019ll have to define an auto_increment<\/code> value and try again. This can be done through phpMyAdmin:<\/p>\n\n\n\n
    \"\"
    Image 4 – AUTO_INCREMENT in phpMyAdmin<\/em><\/figcaption><\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Or running a query like so:<\/p>\n\n\n\n
    ALTER TABLE `demo_table` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;<\/pre><\/div>\n\n\n\n
    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:<\/p>\n\n\n\n
    LOAD DATA INFILE \u2018C:\/wamp64\/tmp\/data1\/MOCK_DATA_1.csv\u2019 IGNORE INTO TABLE\n`demo_table` FIELDS TERMINATED BY \u2018,\u2019 (message,sender,receiver,timestamp);<\/pre><\/div>\n\n\n\n
    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:<\/p>\n\n\n\n
    ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing,\nresulting in duplicate entry '1' for key 'PRIMARY'<\/pre><\/div>\n\n\n\n
    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<\/code> attribute was added. When MySQL attempts to re-sequence the column, it tries to begin from the lowest unused value \u2014 often 1 \u2014 and clashes with an existing row. <\/p>\n\n\n\n
    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<\/code>. If the conflict involves 0, and it\u2019s being treated as a request for an auto-generated value, setting sql_mode = 'NO_AUTO_VALUE_ON_ZERO'<\/code> will restore clarity by ensuring that 0 is treated as a literal value.<\/p>\n\n\n\n
    To avoid this sort of error, run a query like SET SESSION sql_mode=' NO_AUTO_VALUE_ON_ZERO'<\/code>:<\/p>\n\n\n\n
    \n\n\n\n
    \"\"
    Image 5 – Making AUTO_INCREMENT errors go away<\/em><\/figcaption><\/figure>\n\n\n\n
    Using Primary Keys<\/h2>\n\n\n\n
    Let\u2019s now take a look into cases necessitating the usage of our primary keys:<\/p>\n\n\n\n
    \"\"
    Image 6 – Using PKs in SELECT queries<\/em><\/figcaption><\/figure>\n\n\n\n
    <\/p>\n\n\n
    \n
      \n
    1. The first query makes use of a primary key because we only select the id<\/em><\/code> column after the WHERE<\/code> clause \u2013 quite self-explanatory.

      <\/li>\n\n\n\n
    2. The second query doesn\u2019t even consider our primary key because we don\u2019t use it after a WHERE<\/code> clause.

      <\/li>\n\n\n\n
    3. The third query uses our primary key because we know which id<\/code> we\u2019re searching for.

      <\/li>\n\n\n\n
    4. The fourth query behaves the same way – it uses the primary key to locate a specific row.

      <\/li>\n\n\n\n
    5. The last query says \u201cImpossible WHERE noticed after reading const tables\u201d<\/code> which isn\u2019t an error (running such a query is possible and it would return an empty result set), but it denotes that the WHERE<\/code> clause doesn\u2019t quite make sense (in this case, most likely because the id<\/code> of 9,997 doesn\u2019t even exist \u2013 there are around 5,000 rows in the table).<\/li>\n<\/ol>\n<\/div>\n\n\n
      To explain the thinking of our database in operations involving primary keys, we run an ordinary SQL query and add EXPLAIN<\/code> in front of it. The way EXPLAIN<\/code> 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<\/code> lets us in on multiple things:<\/p>\n\n\n
      \n