AUTO_INCREMENT Constraints with MariaDB Xpand

Overview

MariaDB Xpand 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 Xpand can provide better performance by using AUTO_UNIQUE constraints instead of AUTO_INCREMENT.

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

CREATE TABLE

CREATE TABLE and AUTO_INCREMENT

To create a table with an auto-increment column, use the CREATE TABLE statement, and specify the AUTO_INCREMENT column option:

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)
);

ALTER TABLE

ALTER TABLE .. AUTO_INCREMENT

MariaDB Xpand allows the next value for an auto-increment column to be set using the AUTO_INCREMENT table option:

ALTER TABLE hq_sales.invoices AUTO_INCREMENT = 100;

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');

SELECT LAST_INSERT_ID();

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

Data Types for Auto-Increment Columns

The optimal data type for an auto-increment column depends on how large the table is expected to grow:

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

9223372036854775807

Default Values

MariaDB Xpand uses an automatically generated value as the default value for auto-increment columns in the following cases:

  • When an insert statement omits the column

  • When an insert statement specifies 0 as the column's value and sql_mode does not contain NO_AUTO_VALUE_ON_ZERO

  • When an insert statement specifies NULL as the column's value and the column is not defined as NOT NULL

Omit Column

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

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

+------------+
| invoice_id |
+------------+
|          1 |
+------------+

Specify 0

SHOW SESSION VARIABLES LIKE 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+

INSERT INTO hq_sales.invoices
   (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (0, 1, 2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER');

SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
   AND customer_id = 2
   AND invoice_date = '2020-05-10 14:17:32';

+------------+
| invoice_id |
+------------+
|          2 |
+------------+

Specify NULL

INSERT INTO hq_sales.invoices
   (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (NULL, 1, 3, '2020-05-10 14:25:16', 227.15, 'CASH');

SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
   AND customer_id = 3
   AND invoice_date = '2020-05-10 14:25:16';

+------------+
| invoice_id |
+------------+
|          3 |
+------------+

Query the Most Recently Generated Value

MariaDB Xpand provides the LAST_INSERT_ID() function to query the most recently generated value for auto-increment columns in the current session:

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');

SELECT LAST_INSERT_ID();

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

Increment and Offset

MariaDB Xpand provides system variables that modify the increment and offset for AUTO_INCREMENT constraints:

  • auto_increment_increment is used to set the increment

  • auto_increment_offset is used to set the offset

Special Cases

  • MariaDB Xpand requires extra locking to guarantee that it generates consecutive values for auto-increment columns, so using auto-unique can offer better performance than auto-increment

  • MariaDB Xpand can't generate negative values for auto-increment columns