PRIMARY KEY Constraints

Using an AUTO_INCREMENT column as the primary key in MariaDB is beneficial for ensuring unique row identification and efficient data retrieval. This approach automatically generates a unique integer for each new row, simplifying primary key management. It is especially useful in tables where data insertion occurs frequently, as it facilitates quick indexing and minimizes storage overhead due to its numeric nature. This practice helps maintain optimal performance in database operations, particularly when combined with clustered indexing in InnoDB.

Overview

MariaDB Server supports PRIMARY KEY constraints to uniquely identify rows:

There can only be a single primary key for a given table.

• InnoDB uses the primary key as a clustered index, which means that InnoDB stores table data in the order determined by the primary key.

• Primary key indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.

• If no primary key is defined for a table, then InnoDB will use the table's first NOT NULL unique index as the table's primary key.

Using an AUTO_INCREMENT Column as the Primary Key

If your table does not have a column or a set of columns that could act as a natural primary key, then you can define a single AUTO_INCREMENT column, which can serve as the table's primary key. See for more details.

Using a Sequence-backed Column as the Primary Key

If your table does not have a column or a set of columns that could act as a natural primary key, then you can use a sequence to generate an integer value to use as the table's primary key. Sequences were first added in MariaDB Server 10.3 and MariaDB Community Server 10.3. See for more details.

Creating an InnoDB Table with a Single Column Primary Key

Let's key after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement and specify the primary key with the PRIMARY KEY() clause:

For a single column primary key, the primary key can also be specified with the PRIMARY KEY column option:

Creating an InnoDB Table with a Composite Primary Key

Let's create an InnoDB table with a composite (multi-column) primary key after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table using the statement and specify the primary key with the PRIMARY KEY() clause:

Adding a Primary Key to an InnoDB Table

Let's , and then add a primary key to it:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table without a primary key using the statement:

1. Alter the table using the statement and specify the new primary key with the ADD PRIMARY KEY() clause:

Dropping a Primary Key from an InnoDB Table

Let's drop the primary key from the table created in the section:

1. Alter the table using the statement and specify the DROP PRIMARY KEY clause:

Changing a Primary Key for an InnoDB Table

Let's change the primary key from the table created in the section:

1. Alter the table using the statement and specify the DROP PRIMARY KEY clause to drop the old primary key, and specify the new primary key with the ADD PRIMARY KEY() clause:

Finding Tables without Primary Keys

For best performance, every InnoDB table should have a primary key. It is possible to find tables without a primary key using a basic statement.

Let's create an InnoDB table without a primary key, and then use a statement to confirm that it does not have one:

1. Connect to the server using MariaDB Client:

1. Confirm that the default storage engine is InnoDB by checking the using the statement:

1. If the database does not exist, then create the database for the table using the statement:

1. Create the table without a primary key using the statement:

1. Query the .TABLES and tables to find InnoDB tables that do not have a primary key:

1. To add a primary key, alter the table using the statement, and specify the primary key with the ADD PRIMARY KEY() clause:

Handling Duplicate Key Errors

A primary key uniquely identifies every row. Therefore, if a second row is inserted with an identical value, it will fail.

Let's try to insert two identical primary key values into the table created in the section:

1. Insert a row with the statement:

1. Insert a second row that has the same primary key value with the statement:

This will fail with the ER_DUP_ENTRY error code:

1. Fix the problem by inserting the row with a unique primary key value:

To easily generate unique values for a primary key, consider using one of the following options: