ALTER TABLE
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
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} ] ]
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;