PRIMARY KEY Constraints with MariaDB Xpand

Overview

MariaDB Xpand supports PRIMARY KEY constraints to uniquely identify rows:

  • For easy migrations from InnoDB, Xpand handles Primary Keys similar to InnoDB

  • Xpand uses the Primary Key to generate the base representation of the table, which contains all columns in the table and is used for data distribution for high availability and fault tolerance

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

  • Xpand creates a hidden Primary Key when a table does not have an explicit Primary Key

  • For compatibility with InnoDB, Xpand can use AUTO_INCREMENT for Primary Keys

  • For better performance than AUTO_INCREMENT, Xpand can use AUTO_UNIQUE for Primary Keys

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6

CREATE TABLE

CREATE TABLE and Single Column Primary Key Constraint

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a PRIMARY KEY constraint on a single column:

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

Alternatively, the PRIMARY KEY column option can be used for single column primary keys:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
   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')
);

CREATE TABLE and Composite (Multi-Column) Primary Key Constraint

With MariaDB Xpand, the CREATE TABLE statement can be used to create a new table with a PRIMARY KEY constraint on multiple columns composite (multi-column) primary key using the PRIMARY KEY clause:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED 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, branch_id)
);

ALTER TABLE

ALTER TABLE .. ADD PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to add a PRIMARY KEY constraint to an existing table using the ADD PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices
   ADD PRIMARY KEY (invoice_id);

ALTER TABLE .. DROP PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a PRIMARY KEY constraint from an existing table using the DROP PRIMARY KEY clause:

ALTER TABLE hq_sales.invoices
   DROP PRIMARY KEY, LOCK=SHARED;

Locking for the operation can be configured using the LOCK clause.

ALTER TABLE .. PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to alter a PRIMARY KEY constraint on an existing table:

ALTER TABLE hq_sales.invoices
   PRIMARY KEY DISTRIBUTE=3;

Replace a Primary Key

With MariaDB Xpand, the ALTER TABLE statement can be used to replace a PRIMARY KEY constraint in an existing table by combining the DROP PRIMARY KEY and ADD PRIMARY KEY clauses:

ALTER TABLE hq_sales.invoices
   DROP PRIMARY KEY,
   ADD PRIMARY KEY (invoice_id, branch_id);

AUTO_INCREMENT and AUTO_UNIQUE

MariaDB Xpand supports using auto-increment and auto-unique columns as Primary Keys for tables that do not have a natural Primary Key.

For additional information, see "AUTO_INCREMENT Constraints" and "AUTO_UNIQUE Constraints".

Duplicate Key Errors

Primary Keys uniquely identify each row Therefore, if a second row is inserted with an identical primary key value, the insert fails with the ER_DUP_ENTRY error code:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

To easily generate unique values for a primary key, consider using AUTO_INCREMENT or AUTO_UNIQUE.

Special Cases

  • Xpand does not support using the FLOAT data type for primary keys

  • Xpand does not support dropping primary keys using ALTER TABLE .. DROP INDEX PRIMARY, but Xpand does support the operation using ALTER TABLE .. DROP PRIMARY KEY, LOCK=SHARED