AUTO_UNIQUE Constraints with MariaDB Xpand

Overview

MariaDB Xpand supports AUTO_UNIQUE constraints:

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

  • Generated values for auto-unique columns are guaranteed to be unique

  • Auto-unique is a better performing alternative to auto-increment in environments with high levels of concurrency and parallelism:

    • When generating auto-increment values, Xpand requires extra overhead to guarantee that the values are monotonically increasing and consecutive, even when multiple transactions or connections are writing to the same auto-increment columns

    • When generating auto-unique values, Xpand is not required to guarantee that the values are monotonically increasing and consecutive, so there is less overhead

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6

CREATE TABLE

CREATE TABLE and AUTO_UNIQUE

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

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT(0) UNSIGNED AUTO_UNIQUE 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

Migrate from AUTO_INCREMENT to AUTO_UNIQUE

To migrate an auto-increment column to auto-unique, use the ALTER TABLE ... MODIFY COLUMN statement, and specify the AUTO_UNIQUE column option:

ALTER TABLE hq_sales.invoices
   MODIFY COLUMN invoice_id BIGINT(0) UNSIGNED AUTO_UNIQUE NOT NULL;

Data Types for Auto-Unique Columns

Auto-unique columns must be of type BIGINT(0) UNSIGNED.

Default Values

MariaDB Xpand uses an automatically generated value as the default value for auto-unique 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 |
+------------+

Special Cases

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