ALTER TABLE with MariaDB Enterprise Server
This page is part of MariaDB's Documentation.
The parent of this page is: Table DDL for MariaDB Enterprise Server
Topics on this page:
Overview
MariaDB Enterprise Server supports the ALTER TABLE statement to alter, change, or modify tables:
Enterprise Server accepts table options in
ALTER TABLEstatements to alter table characteristicsEnterprise Server allows concurrent read and write queries during
ALTER TABLEstatements
Compatibility
MariaDB Enterprise Server 10.2
MariaDB Enterprise Server 10.3
MariaDB Enterprise Server 10.4
MariaDB Enterprise Server 10.5
MariaDB Enterprise Server 10.6
Index-Related Operations
ALTER TABLE .. ADD INDEX
With MariaDB Enterprise Server, 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 Enterprise Server, 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 Enterprise Server, 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 Enterprise Server, 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 Enterprise Server, 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;
Primary Key-Related Operations
ALTER TABLE .. ADD PRIMARY KEY
With MariaDB Enterprise Server, 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 Enterprise Server, 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;
Replace a Primary Key
With MariaDB Enterprise Server, 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);
Foreign Key-Related Operations
ALTER TABLE .. ADD CONSTRAINT .. FOREIGN KEY
With MariaDB Enterprise Server, 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 Enterprise Server, 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;
Table Options
With MariaDB Enterprise Server, the ALTER TABLE statement can be used to set table options:
ALTER TABLE hq_sales.invoices
ROW_FORMAT=DYNAMIC;