MariaDB Xpand Primary Keys

Tables use Primary Keys to uniquely identify each row.

Overview

MariaDB Xpand supports primary keys:

  • The behavior of primary keys with Xpand is identical to the behavior of primary keys with InnoDB.

  • A given table can only have a single Primary Key.

  • 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.

  • Primary Key indexes are B+ trees, which are very efficient for searching for exact values, performing range scans, and checking uniqueness.

  • When the user does not specify a Primary Key, Xpand creates a hidden Primary Key.

  • For best performance, users should always create Primary Keys for their tables.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Examples

Create a Table with a Primary Key

To create a table with a primary key, use the CREATE TABLE statement with 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)
);

Note

If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Using Auto-Incremented Columns

If your table does not have a column or a set of columns that could act as a natural Primary Key, then you can define a single AUTO_INCREMENT column, which can serve as the table's Primary Key. See Using Auto-Incrementing Columns with Xpand for more details.

Creating Tables with Single Column Primary Keys

Let's create an Xpand table with a single column Primary Key:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the PRIMARY KEY clause to choose a primary key:

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

    For a single column Primary Key, the Primary Key can also be specified with the PRIMARY KEY column option:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Creating Tables with Composite Primary Keys

Let's create an Xpand table with a composite (multi-column) Primary Key:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the PRIMARY KEY clause to choose the primary key:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Adding Primary Keys

Let's create an Xpand table without a Primary Key, and then add a Primary Key to it:

  1. Connect to the server using MariaDB Client:

    $ mysql --user=root --password --no-auto-rehash
    
  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table without a primary key:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

  4. Alter the table using the ALTER TABLE statement and specify the new primary key with the ADD PRIMARY KEY clause:

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

Dropping Primary Keys

Let's drop the Primary Key from the table created in the Creating Xpand Tables with Single Column Primary Keys section:

  1. Alter the table using the ALTER TABLE statement and specify the DROP PRIMARY KEY clause:

    ALTER TABLE hq_sales.invoices DROP PRIMARY KEY;
    

Changing Primary Keys

Let's change the Primary Key from the table created in the Creating Xpand Tables with Single Column Primary Keys section:

  1. Alter the table using the ALTER TABLE statement and specify the DROP PRIMARY KEY clause to drop the old primary key, and specify the new primary key with the ADD PRIMARY KEY clause:

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

Handling Duplicate Key Errors

Primary Keys uniquely identify each row Therefore, if a second row is inserted with an identical value, it fails.

Let's try to insert two identical Primary Key values into the table created in the Creating Xpand Tables with Single Column Primary Key section:

  1. Insert a row with the INSERT statement:

    INSERT INTO hq_sales.invoices
       (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 1, 1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
    
  2. Insert a second row that has the same primary key value with the INSERT statement:

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

    This will fail with the ER_DUP_ENTRY error code:

    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
    
  3. Fix the problem by inserting the row with a unique primary key value:

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

    To easily generate unique values for a primary key, consider using one of the following options: