AUTO_INCREMENT Constraints

To define an AUTO_INCREMENT column, select an integer data type to accommodate the range of anticipated values. Common choices include INT, BIGINT, TINYINT, SMALLINT, and MEDIUMINT, depending on size requirements. Ensure the chosen type provides sufficient capacity to avoid overflow while optimizing storage efficiency.

Overview

MariaDB Enterprise Server supports AUTO_INCREMENT constraints:

• A column with an AUTO_INCREMENT constraint can act as a table's primary key when a natural primary key is not available

• Generated values for auto-increment columns are guaranteed to be unique and monotonically increasing

• Auto-increment columns provide compatibility with schemas designed for MariaDB Enterprise Server and MySQL

Alternatively, MariaDB Enterprise Server can use as the primary key instead of columns with AUTO_INCREMENT constraints. Sequences are compliant with the SQL standard, while AUTO_INCREMENT constraints are not, so sequences are the better option for applications that require standard-compliant features.

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.

1. 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:

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 after confirming that the 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:

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.

Omitting the Column

If the column is not specified, then InnoDB will automatically generate the value.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

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

1. Select the same row with the statement to confirm that a value was automatically generated:

Specifying the Column's Value as 0

If the column's value is specified as 0, then InnoDB will automatically generate the value if the system variable does not contain NO_AUTO_VALUE_ON_ZERO.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

1. Confirm that the session's value of the sql_mode system variable does not contain NO_AUTO_VALUE_ON_ZERO with the statement:

1. Insert a row with the statement, and specify the AUTO_INCREMENT column's value as 0:

1. Select the same row with the statement to confirm that a value was automatically generated:

Specifying the Column's Value as NULL

If the column's value is specified as NULL, then InnoDB will automatically generate the value if the column is defined as NOT NULL.

Let's insert a row into the table created in the section:

1. Connect to the server using MariaDB Client:

1. Insert a row with the statement, and specify the AUTO_INCREMENT column's value as NULL:

1. Select the same row with the statement to confirm that a value was automatically generated:

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 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 section and then use the function:

1. Connect to the server using MariaDB Client:

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

1. Execute the function to get the AUTO_INCREMENT value for the new row:

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

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 system variable. Users can choose between 3 different values:

• In interleaved lock mode, InnoDB never holds a table-level lock while generating AUTO_INCREMENT values. • Interleaved lock mode is not safe to use if binlog_format is set to STATEMENT.|

Configuring the AUTO_INCREMENT Lock Mode for InnoDB

The system variable configures the AUTO_INCREMENT Lock Mode for InnoDB.

1. Choose a configuration file for custom changes to system variables and options.

It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Use the z- prefix in the file name to ensure that your custom configuration file is read last.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file. It needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

For example:

1. Restart the server:

Setting a Table's Next AUTO_INCREMENT Value

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

Let's alter the AUTO_INCREMENT value for the table created in the 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:

1. Alter the table's next AUTO_INCREMENT value with the statement:

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

1. Execute the function to get the AUTO_INCREMENT value for the new row:

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

Configure the Offset and Increment Values

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

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