ALTER TABLE

ALTER TABLE is used to modify the structure of an existing table.

Syntax Description

ALTER TABLE is used to modify the structure of an existing table.

Syntax Structure

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
    ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {[INDEX | KEY] [index_name]
        [index_option] ...
  | ADD [CONSTRAINT] UNIQUE [INDEX | KEY]
        [index_name][index_option] ...
  | ADD [CONSTRAINT] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DROP PRIMARY KEY
  | RENAME [TO | AS] new_tbl_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | RENAME COLUMN old_col_name TO new_col_name
    }

key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
  COMMENT[=]'string'
}
table_options:
    table_option [[,] table_option] ...
table_option: {
  COMMENT [=] 'string'
}

Syntax Explanation

Below are explanations for each parameter:

  1. ALTER TABLE tbl_name: Modifies the table named tbl_name.

  2. alter_option: Specifies one or more alteration options, separated by commas.

    • table_options: Used to set or modify table options, such as table comments (COMMENT).

    • ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]: Adds a new column to the table, optionally specifying its position (before or after another column).

    • ADD [COLUMN] (col_name column_definition,...): Adds multiple columns simultaneously.

    • ADD {[INDEX | KEY] [index_name] [index_option] ...: Adds an index, optionally specifying the index name and options (e.g., comments).

    • ADD [CONSTRAINT] UNIQUE [INDEX | KEY] [index_name][index_option] ...: Adds a UNIQUE constraint or UNIQUE index.

    • ADD [CONSTRAINT] FOREIGN KEY [index_name] (col_name,...) reference_definition: Adds a FOREIGN KEY constraint.

    • ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...): Adds a PRIMARY KEY constraint.

    • CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]: Modifies a column’s definition, name, and position.

    • ALTER INDEX index_name {VISIBLE | INVISIBLE}: Changes the visibility of an index.

    • DROP [COLUMN] col_name: Drops a column.

    • DROP {INDEX | KEY} index_name: Drops an index.

    • DROP FOREIGN KEY fk_symbol: Drops a FOREIGN KEY constraint.

    • DROP PRIMARY KEY: Drops the PRIMARY KEY.

    • RENAME [TO | AS] new_tbl_name: Renames the table.

    • MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]: Modifies a column’s definition and position.

    • RENAME COLUMN old_col_name TO new_col_name: Renames a column.

  3. key_part: Specifies the components of an index. For text columns, you can optionally specify a length for the index. If no length is specified, the entire column value is used, which may impact performance for large text or binary columns.

  4. index_option: Specifies index options, such as comments (COMMENT).

  5. table_options: Specifies table options, such as comments (COMMENT).

  6. table_option: Specific table options, such as comments (COMMENT).

  7. ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}: Specifies the algorithm MatrixOne uses to perform the operation. COPY builds a new copy of the table; INPLACE modifies the table in place; INSTANT applies metadata-only changes; DEFAULT lets MatrixOne pick the algorithm for the operation.

  8. LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}: Specifies the lock level required during the operation. NONE allows concurrent reads and writes, SHARED allows concurrent reads but not writes, EXCLUSIVE blocks concurrent access, and DEFAULT lets MatrixOne pick the lock level.

ALGORITHM and LOCK Clauses

ALTER TABLE accepts the MySQL-compatible ALGORITHM and LOCK clauses. They can appear alone or together, before the table alteration operation:

ALTER TABLE tbl_name ALGORITHM=COPY, LOCK=SHARED, ADD COLUMN c INT;

ALGORITHM accepts DEFAULT, INSTANT, INPLACE, and COPY. LOCK accepts DEFAULT, NONE, SHARED, and EXCLUSIVE.

Not every combination is valid. Operations that rebuild the table, such as ADD COLUMN and DROP COLUMN, require ALGORITHM=COPY and reject ALGORITHM=INPLACE or ALGORITHM=INSTANT. Operations that only touch metadata or indexes, such as ADD INDEX, DROP INDEX, and RENAME COLUMN, accept INPLACE or INSTANT. When an operation requires COPY, LOCK=NONE is rejected because COPY needs an exclusive lock.

DROP DATABASE IF EXISTS alter_table_alg_demo;
CREATE DATABASE alter_table_alg_demo;
USE alter_table_alg_demo;

CREATE TABLE t_alg_lock(a INT, b INT);
INSERT INTO t_alg_lock VALUES (1, 2);

ALTER TABLE t_alg_lock ALGORITHM=COPY, ADD COLUMN c1 INT;
ALTER TABLE t_alg_lock ALGORITHM=INPLACE, ADD INDEX idx_s2(a);
ALTER TABLE t_alg_lock LOCK=SHARED, ADD INDEX idx_s4(b);
ALTER TABLE t_alg_lock LOCK=EXCLUSIVE, ADD COLUMN c3 INT;
ALTER TABLE t_alg_lock ALGORITHM=INSTANT, LOCK=DEFAULT, RENAME COLUMN c3 TO c3_new;

DROP DATABASE alter_table_alg_demo;

Examples

  • Example 1: Dropping a FOREIGN KEY constraint

DROP DATABASE IF EXISTS alter_table_fk_demo;
CREATE DATABASE alter_table_fk_demo;
USE alter_table_fk_demo;

-- Create table f1 with two integer columns: fa (PRIMARY KEY) and fb (UNIQUE KEY)
CREATE TABLE f1(fa INT PRIMARY KEY, fb INT UNIQUE KEY);
-- Create table c1 with two integer columns: ca and cb
CREATE TABLE c1 (ca INT, cb INT);
-- Add a FOREIGN KEY constraint named ffa to c1, linking column ca to f1.fa
ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
-- Insert a record into f1: (2, 2)
INSERT INTO f1 VALUES (2, 2);
-- Inserting ca = 1 into c1 violates the foreign key because 1 is not present in f1.fa
-- Expected-Success: false
INSERT INTO c1 VALUES (1, 1);
-- Insert a record into c1: (2, 2)
INSERT INTO c1 VALUES (2, 2);
-- Select all records from c1, ordered by ca
SELECT ca, cb FROM c1 ORDER BY ca;
-- Drop the FOREIGN KEY constraint ffa from c1
ALTER TABLE c1 DROP FOREIGN KEY ffa;
-- Insert a record into c1: (1, 1)
INSERT INTO c1 VALUES (1, 1);
-- Select all records from c1, ordered by ca
SELECT ca, cb FROM c1 ORDER BY ca;

DROP DATABASE alter_table_fk_demo;

  • Example 2: Adding a PRIMARY KEY

DROP DATABASE IF EXISTS alter_table_pk_demo;
CREATE DATABASE alter_table_pk_demo;
USE alter_table_pk_demo;

-- Create table t1 with columns a (INTEGER), b (CHAR(10)), c (DATE), d (DECIMAL(7,2)), and a UNIQUE KEY on (a, b)
CREATE TABLE t1(a INTEGER, b CHAR(10), c DATE, d DECIMAL(7,2), UNIQUE KEY(a, b));

-- Insert three records into t1
INSERT INTO t1 VALUES(1, 'ab', '1980-12-17', 800);
INSERT INTO t1 VALUES(2, 'ac', '1981-02-20', 1600);
INSERT INTO t1 VALUES(3, 'ad', '1981-02-22', 500);

-- Display all records from t1
SELECT * FROM t1;

-- Add a PRIMARY KEY named pk1 on columns (a, b)
ALTER TABLE t1 ADD PRIMARY KEY pk1(a, b);

-- View the modified structure of t1
DESC t1;

-- Display all records from t1 after adding the PRIMARY KEY
SELECT * FROM t1;

DROP DATABASE alter_table_pk_demo;

  • Example 3: Renaming a Column

DROP DATABASE IF EXISTS alter_table_rename_col_demo;
CREATE DATABASE alter_table_rename_col_demo;
USE alter_table_rename_col_demo;

CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10));

INSERT INTO t1 VALUES(1, 'ab');
INSERT INTO t1 VALUES(2, 'ac');
INSERT INTO t1 VALUES(3, 'ad');

SELECT * FROM t1;

-- Rename column a to x and change its data type to VARCHAR(20)
ALTER TABLE t1 CHANGE a x VARCHAR(20);

DESC t1;
SELECT * FROM t1;

DROP DATABASE alter_table_rename_col_demo;

  • Example 4: Renaming a Table

DROP DATABASE IF EXISTS alter_table_rename_demo;
CREATE DATABASE alter_table_rename_demo;
USE alter_table_rename_demo;

CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10));

SHOW TABLES;

ALTER TABLE t1 RENAME TO t2;

SHOW TABLES;

DROP DATABASE alter_table_rename_demo;

Limitations

  1. CHANGE [COLUMN], MODIFY [COLUMN], RENAME COLUMN, ADD [CONSTRAINT [symbol]] PRIMARY KEY, DROP PRIMARY KEY, ADD COLUMN, and DROP COLUMN can be combined in an ALTER TABLE statement with the following restriction: DROP PRIMARY KEY cannot be combined with RENAME COLUMN, CHANGE COLUMN, or DROP COLUMN (causes a server panic). DROP PRIMARY KEY combined with ADD COLUMN or MODIFY COLUMN works correctly.

  2. Temporary tables do not currently support structural modifications via ALTER TABLE.