CREATE TABLE with MariaDB Xpand

Overview

MariaDB Xpand supports the CREATE TABLE statement to create tables:

  • Xpand accepts table options in CREATE TABLE statements to define table characteristics

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

CREATE TABLE

To create a table with MariaDB Xpand, use the CREATE TABLE statement:

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)
) REPLICAS=3;

Special Cases

  • Xpand accepts the CHECK constraint syntax in CREATE TABLE, but no constraint checking is performed

  • Xpand allows a default ENUM value to be specified using the position:

    • CREATE TABLE CountryLanguage (id INT, IsOfficial ENUM('T','F') NOT NULL default 1);

  • Xpand allows a default ENUM value to be specified that is not in the list of possible values

    • CREATE TABLE Sunny (Pig ENUM('little', 'big') DEFAULT 'pink');

  • Xpand does not trim trailing whitespace in ENUM values

  • Xpand does not raise an error if a NOT NULL column specifies NULL as the default value, but the column is treated as NOT NULL

  • Xpand does not raise an error if the length for DECIMAL exceeds the maximum precision and will silently truncate, even if sql_mode=STRICT_TRANS_TABLES is enabled

  • Xpand does not raise an error when the default value for an INT column is set to a quoted string, but inserts with the default value will be cast to 0

  • Xpand does not support the % wildcard in database names