AUTO_INCREMENT Constraints with MariaDB Enterprise Server

Compatibility

• MariaDB Enterprise Server 10.2

• MariaDB Enterprise Server 10.3

• MariaDB Enterprise Server 10.4

• MariaDB Enterprise Server 10.5

• MariaDB Enterprise Server 10.6

Choosing a Data Type for an AUTO_INCREMENT Column

When designing a schema, AUTO_INCREMENT columns should use integer data types. The following types can be used:

To determine which type to use, consider the following points:

1. Do you want to be able to manually insert negative values? If not, then specify the UNSIGNED attribute for the column.

   InnoDB can't generate negative AUTO_INCREMENT values, so it is only beneficial to use a signed integer column if you want the option to manually insert negative values, which would bypass the AUTO_INCREMENT handling.

2. How large will your table grow?

   If your AUTO_INCREMENT column is being used as the table's primary key, then the maximum value for the chosen data type should be considered the maximum number of rows that can fit in the table:

   Data Type	Maximum Number of Rows (Signed)	Maximum Number of Rows (Unsigned)
   TINYINT	127	255
   SMALLINT	32767	65535
   MEDIUMINT	8388607	16777215
   INT	2147483647	4294967295
   BIGINT	9223372036854775807	18446744073709551615

   If you want to give your table the most room to grow, then it would be best to choose BIGINT UNSIGNED.

Creating an InnoDB Table with an AUTO_INCREMENT Column

Let's create an InnoDB table with an AUTO_INCREMENT column after confirming that the default storage engine is InnoDB:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

   SHOW SESSION VARIABLES
      LIKE 'default_storage_engine';
   

   +------------------------+--------+
   | Variable_name          | Value  |
   +------------------------+--------+
   | default_storage_engine | InnoDB |
   +------------------------+--------+
   

3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

   CREATE DATABASE hq_sales;
   

4. Create the table using the CREATE TABLE statement:

   CREATE TABLE hq_sales.invoices (
      invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
      branch_id INT NOT NULL,
      customer_id INT,
      invoice_date DATETIME(6),
      invoice_total DECIMAL(13, 2),
      payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
      PRIMARY KEY(invoice_id)
   );
   

Inserting a Row with an AUTO_INCREMENT Column

If a column is specified as AUTO_INCREMENT, then its value will be automatically generated. There are multiple ways to insert rows with these automatically generated values.

Getting the Last Inserted AUTO_INCREMENT Value

After InnoDB inserts an automatically generated value into an AUTO_INCREMENT column, the application sometimes needs to know what value it inserted. For example, the application may need to use the value to insert a foreign key column in a dependent table. The LAST_INSERT_ID() function can be used to get the lasted inserted value for an AUTO_INCREMENT column without re-reading the row from the table.

Let's insert a row into the table created in the Creating an InnoDB Table with an AUTO_INCREMENT Column section and then use the LAST_INSERT_ID() function:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Insert a row with the INSERT statement, but do not specify the AUTO_INCREMENT column:

   INSERT INTO hq_sales.invoices
      (branch_id, customer_id, invoice_date, invoice_total, payment_method)
   VALUES
      (1, 4, '2020-05-10 12:37:22', 104.19, 'CREDIT_CARD');
   

3. Execute the LAST_INSERT_ID() function to get the AUTO_INCREMENT value for the new row:

   SELECT LAST_INSERT_ID();
   

   +------------------+
   | LAST_INSERT_ID() |
   +------------------+
   |                4 |
   +------------------+
   

4. Select the same row with the SELECT statement to confirm that the AUTO_INCREMENT column has the same value:

   SELECT invoice_id
   FROM hq_sales.invoices
   WHERE branch_id = 1
   AND customer_id = 4
   AND invoice_date = '2020-05-10 12:37:22';
   

   +------------+
   | invoice_id |
   +------------+
   |          4 |
   +------------+
   

Choosing an AUTO_INCREMENT Lock Mode for InnoDB

When multiple rows are inserted into a table concurrently, InnoDB needs to be able to generate multiple values concurrently in a safe manner. It has several different modes that can be used to do this, and each mode has its own advantages and disadvantages.

InnoDB's AUTO_INCREMENT lock mode is configured with the innodb_autoinc_lock_mode system variable. Users can choose between 3 different values:

Setting a Table's Next AUTO_INCREMENT Value

A table's next AUTO_INCREMENT value can be set with the ALTER TABLE statement. The value is set using the AUTO_INCREMENT table option.

Let's alter the AUTO_INCREMENT value for the table created in the Creating an InnoDB Table with an AUTO_INCREMENT Column section and then insert a row into the table, so we can confirm that it uses the new value:

1. Connect to the server using MariaDB Client:

   $ mariadb --user=root
   

2. Alter the table's next AUTO_INCREMENT value with the ALTER TABLE statement:

   ALTER TABLE hq_sales.invoices
      AUTO_INCREMENT = 100;
   

3. Insert a row with the INSERT statement, but do not specify the AUTO_INCREMENT column:

   INSERT INTO hq_sales.invoices
      (branch_id, customer_id, invoice_date, invoice_total, payment_method)
   VALUES
      (1, 5, '2020-05-10 12:43:19', 1105.98, 'CREDIT_CARD');
   

4. Execute the LAST_INSERT_ID() function to get the AUTO_INCREMENT value for the new row:

   SELECT LAST_INSERT_ID();
   

   +------------------+
   | LAST_INSERT_ID() |
   +------------------+
   |              100 |
   +------------------+
   

5. Select the same row with the SELECT statement to confirm that the AUTO_INCREMENT column has the same value:

   SELECT invoice_id
   FROM hq_sales.invoices
   WHERE branch_id = 1
   AND customer_id = 5
   AND invoice_date = '2020-05-10 12:43:19';
   

   +------------+
   | invoice_id |
   +------------+
   |        100 |
   +------------+
   

Configure the Offset and Increment Values

The offset and increment values can be configured by setting the auto_increment_offset and auto_increment_increment system variables.

When Galera ClusterGalera Cluster is used, the offset and increment values are managed automatically by default. They can be managed manually by disabling the wsrep_auto_increment_control system variable.