AUTO_UNIQUE
Constraints with MariaDB Xpand
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Constraints for MariaDB Xpand
Topics on this page:
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 availableGenerated 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 andsql_mode
does not containNO_AUTO_VALUE_ON_ZERO
When an insert statement specifies
NULL
as the column's value and the column is not defined asNOT 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