UNIQUE Constraints with MariaDB Xpand

Overview

MariaDB Xpand supports UNIQUE constraints to ensure that a column's value is unique within a table:

  • Xpand uses unique indexes to speed up query execution and enforce unique constraints

  • Xpand supports single column and composite (multi-column) unique indexes

  • Xpand generates a representation for each unique index

  • Xpand stores unique indexes as B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness

  • Xpand creates replicas for each unique index's representation to guarantee fault tolerance

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

CREATE INDEX

With MariaDB Xpand, the CREATE UNIQUE INDEX statement can be used to create a new unique index on an existing table:

CREATE UNIQUE INDEX idx_invoices_branch_customer
   ON hq_sales.invoices (branch_id, customer_id);

DROP INDEX

With MariaDB Xpand, the DROP INDEX statement can be used to drop a unique index from an existing table:

DROP INDEX idx_invoices_branch_customer
   ON hq_sales.invoices;

CREATE TABLE

CREATE TABLE and Single Column Unique Indexes

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a single column unique index using the UNIQUE INDEX clause:

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'),
   auth_code varchar(128),
   PRIMARY KEY(invoice_id),
   UNIQUE INDEX idx_invoices_auth (auth_code)
);

Alternatively, the UNIQUE column option can be used for single column unique indexes:

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'),
   auth_code varchar(128) UNIQUE,
   PRIMARY KEY(invoice_id),
);

CREATE TABLE and Composite (Multi-Column) Unique Indexes

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a composite (multi-column) unique index using the UNIQUE INDEX clause:

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),
   UNIQUE INDEX idx_invoices_branch_customer (branch_id, customer_id)
);

ALTER TABLE

ALTER TABLE .. ADD UNIQUE INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to create a new unique index on an existing table using the ADD UNIQUE INDEX clause:

ALTER TABLE hq_sales.invoices
   ADD UNIQUE INDEX idx_invoices_date_total (invoice_date, invoice_total);

ALTER TABLE .. ALTER INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to alter an existing unique index using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER INDEX idx_invoices_date_total (invoice_date, invoice_total) INVISIBLE;

ALTER TABLE .. DROP INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a unique index from an existing table using the DROP INDEX clause:

ALTER TABLE hq_sales.invoices
   DROP INDEX idx_invoices_branch_customer;

ALTER TABLE .. RENAME INDEX

With MariaDB Xpand, the ALTER TABLE statement can be used to rename a unique index using the RENAME INDEX clause:

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_branch_customer
   TO idx_invoices_branch_customer_old;

Temporarily Disable UNIQUE Constraint Checks

When performing bulk data loads, dropping a table, or rebuilding a table, it can improve performance to temporarily disable UNIQUE constraint checks:

  • If UNIQUE constraint checks are temporarily disabled, then rows can be inserted that violate the constraint, so users must proceed with caution

  • This feature is most useful when you are loading a data set that is known to be valid

  • UNIQUE constraint checks can be temporarily disabled by setting the session value for the unique_checks system variable to OFF:

    SET SESSION unique_checks=OFF;

Special Cases

  • Xpand allows longer unique keys than MySQL