ALTER TABLE

Overview

Modifies table characteristics, including changing the name, reordering the columns, and changing column data types.

USAGE

Common Syntax:

ALTER TABLE <tbl_name>
   [ [alter_table_commands] ... [alter_partition_function] ...]

ALTER TABLE <tbl_name>
   LAZY PROTECT
   [REPLICAS [=] <value>]

ALTER TABLE <tbl_name>
   DROP PARTITION [ ALL | <col_list> [, <col_list> ...] ]

ALTER TABLE <tbl_name>
   TRUNCATE PARTITION [ ALL | <col_list> [, <col_list> ...] ]

ALTER TABLE <tbl_name>
   ADD PARTITION ([partition_def_list] ...)

ALTER TABLE <tbl_name>
   REORGANIZE PARTITION (partition_list ...)
   INTO ([partition_def_list>] ...)

ALTER TABLE <tbl_name>
   EXCHANGE PARTITION <partition_name> WITH TABLE <tbl_name>
   [ {WITH | WITHOUT} VALIDATION ]

Alter Table Commands:

ADD COLUMN <col_name> [<col_definition>] ...
ADD COLUMN <col_definition> (,[<col_definition>] ...)
CHANGE COLUMN <col_name> [<col_definition>] ...
MODIFY COLUMN [<col_definition>] ...
AlTER COLUMN <col_name> SET DEFAULT <expression>
ALTER COLUMN <col_name> DROP DEFAULT
DROP COLUMN <col_name> [CASCADE | RESTRICT]
ALTER COLUMN <col_name> [TYPE] <type_value> USING <expression>
ADD <table_constraint>
DROP CONSTRAINT [IF EXIST] <constraint_name> [CASCADE | RESTRICT]
DROP [KEY | INDEX] [IF EXISTS] <index_name>
DROP FOREIGN KEY <key_name>
DROP PRIMARY <key_index>
[ENABLE KEYS | DISABLE KEYS]
[ENABLE INSERT_LOCK | DISABLE INSERT_LOCK]
RENAME TO <table_name>
RENAME [KEY | INDEX] <index_name> TO <index_name>
ALTER [KEY | INDEX] <index_name> [<index_option>] ...
PRIMARY KEY [<index_option>] ...
[ [<table_option>] ... ]
ALGORITHM [DEFAULT | INPLACE | COPY]
[ LOCK [ {DEFAULT | NONE | SHARED | EXCLUSIVE} ] ]
[NOFLIP]

Index Options:

REPLICAS [=] {<int> | ALLNODES}
| REPLICAS [=] <int>
| HASHES [=] (<int>[, <int> ...])
| PAYLOAD [=] <paren_repcol_list>
| [ USING {LAYERED | HASH | RTREE | SKIPLIST | COLUMNAR | BTREE} ]
| SLICES [=] <int>
| [ DEFAULT_SLICES [=] [ <int> | [NULL] ] ]
| [CONTAINER] [COLUMNAR | LAYERED | SKIPLIST | BTREE | COLUMNAR_LAYERED]
| DISTRIBUTE [=] <int>
| KEY_BLOCK_SIZE [=] <int>
| SPLIT_THRESHOLD_KB [=] <int>
| COMMENT [=] <string>
| [VISIBLE | INVISIBLE]
| [AUTO_STATISTICS] [=] <auto_pd_set>

Supported Table Options:

REPLICAS [=] {<int> | ALLNODES}
| SLICES [=] <int>
| [DEFAULT] SLICES [=] [<int> | NULL]
| BUILDING
| HASHES [=] (<int>[, <int> ...])
| INSERT_LOCK
| PAYLOAD
| SPLIT_THRESHOLD_KB [=] <int>
| [ENGINE | TYPE] [=] <string>
| CONTAINER = [COLUMNAR | LAYERED | SKIPLIST | BTREE | COLUMNAR_LAYERED]
| [DEFAULT] CHARACTER SET <xpand_charset_name>
| AUTO_STATISTICS [=] <auto_pd_set>
| AVG_ROW_LENGTH = <int>
| COMMENT [=] <string>
| [DEFAULT] COLLATE [=] <xpand_collation_name>

Ignored Table Options:

CHECKSUM [=] <string>
| CONNECTION [=] <string>
| DELAY_KEY_WRITE [=] <int>
| INSERT_METHOD [NO | FIRST | LAST]
| KEY_BLOCK_SIZE [=] <int>
| MAX_ROWS [=] <int>
| MIN_ROWS [=] <int>
| PACK_KEYS [0 | 1 | DEFAULT]
| PAGE_CHECKSUM [=] <string>
| PASSWORD [=] <string>
| ROW_FORMAT [DEFAULT]
| TABLESPACE <tbl_name> [DEFAULT <col_id> STORAGE]
| UNION (<tbl_name>, ...)

Alter Partition Function:

PARTITION BY RANGE
   (<expression>) COLUMNS (, <expression> ...)
   [<partition_definition>] ...]
   [REMOVE PARTITIONING]

Partition definition:

PARTITION <partition_name> [VALUES  {LESS THAN {(expression) | MAXVALUE} | IN (value_list>}
   [partition_option] ...
   [partition_def_index_option_list] ...

Partition definition index option list:

[KEY | INDEX] <index_name> [SLICES [=] <int>]

Partition option:

[ [ENGINE | TYPE] [=] <string> ]
[COMMENT [=] <string>]
[MAX_ROWS [=] <int>]
[MIN_ROWS [=] <int>]
[SLICES [=] <int>]

Column definitions:

[ <col_name> ENUM (<list_value>) SET (<list_value>)
   [CONSTRAINT] <constraint_name> [<col_constraint_elem>] ... ]

[ <col_name> SERIAL [CONSTRAINT] <constraint_name> [<col_constraint_elem>] ... ]

[ <col_name> <type_value> COLLATE <collation_name>
   GENERATED ALWAYS AS (<expression>)
   [VIRTUAL | STORED]
   [CONSTRAINT] <constraint_name> CHECK (<expression>) ]

[ <col_name> <type_value> GENERATED ALWAYS AS (<expression>)
   [VIRTUAL | STORED]
   [ [CONSTRAINT] <constraint_name> CHECK (<expression>) ] ]

Column constraint element:

[NOT [NULL] | NULL_ALT]
| [[NULL] | NULL_ALT]
| [UNIQUE KEY | FULLTEXT KEY]
| [UNIQUE]
| [PRIMARY KEY]
| [CHECK (<expression>)]
| REFERENCES <ref_name> <col_name> [, <col_name> ...] <key_match> <key_action>

Key match:

[MATCH FULL | MATCH SIMPLE]

Key action:

[ ON UPDATE [ {NO | ACTION | RESTRICT | CASCADE | SET [NULL] | SET DEFAULT} ] ]
| [ ON DELETE [ {NO | ACTION | RESTRICT | CASCADE | SET [NULL] | SET DEFAULT} ] ]

DETAILS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

ALTER TABLE .. ADD COLUMNAR INDEX

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

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

For best performance, MariaDB recommends loading data into the table prior to adding a Columnar index.

ALTER TABLE .. ADD INDEX

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

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

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 index using the ALTER INDEX clause:

ALTER TABLE hq_sales.invoices
   ALTER COLUMNAR 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 an index from an existing table using the DROP INDEX clause:

ALTER TABLE hq_sales.invoices
   DROP INDEX idx_invoices_date_total;

ALTER TABLE .. RENAME INDEX

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

ALTER TABLE hq_sales.invoices
   RENAME INDEX idx_invoices_date_total
   TO idx_invoices_date_total_old;

ALTER TABLE .. ADD PRIMARY KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to create a primary key on 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 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 on an existing table using the PRIMARY KEY clause:

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

ALTER TABLE .. ADD CONSTRAINT .. FOREIGN KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to add a foreign key using the ADD FOREIGN KEY clause with an optional constraint name:

ALTER TABLE hq_sales.invoices
   ADD CONSTRAINT fk_invoices_customers
      FOREIGN KEY (customer_id)
      REFERENCES hq_sales.customers (customer_id)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT;

ALTER TABLE .. DROP FOREIGN KEY

With MariaDB Xpand, the ALTER TABLE statement can be used to drop a foreign key using the DROP FOREIGN KEY clause:

ALTER TABLE hq_sales.invoices
   DROP FOREIGN KEY fk_invoices_customers;

Set Table Options

With MariaDB Xpand, the ALTER TABLE statement can be used to set table options:

ALTER TABLE hq_sales.invoices
   REPLICAS=3;

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES