ALTER TABLE

Description

ALTER TABLE is used to modify the existing data table structure.

Syntax

  1. ALTER TABLE tbl_name
  2.     [alter_option [, alter_option] ...]
  4. alter_option: {
  5.     table_options
  6.   | ADD [COLUMN] col_name column_definition
  7.         [FIRST | AFTER col_name]
  8.   | ADD [COLUMN] (col_name column_definition,...)
  9.   | ADD {[INDEX | KEY] [index_name]
  10.         [index_option] ...
  11.   | ADD [CONSTRAINT] UNIQUE [INDEX | KEY]
  12.         [index_name][index_option] ...
  13.   | ADD [CONSTRAINT] FOREIGN KEY
  14.         [index_name] (col_name,...)
  15.         reference_definition
  16.     }
  17.   | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  18.   | DROP [COLUMN] col_name
  19.   | DROP {INDEX | KEY} index_name
  20.   | DROP FOREIGN KEY fk_symbol
  21.   | RENAME [TO | AS] new_tbl_name
  22. }
  24. key_part: {col_name [(length)] | (expr)} [ASC | DESC]
  25. index_option: {
  26.   COMMENT[=]'string'
  27. }
  28. table_options:
  29.     table_option [[,] table_option] ...
  30. table_option: {
  31.   COMMENT [=] 'string'
  32. }

Explanations

The explanations of each parameter are as the following:

  1. ALTER TABLE tbl_name: Indicates modifying a table named tbl_name.

  2. alter_option: Indicates that one or more change options can be executed, 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, specifying the position of the new column (before or after a particular column).
    • ADD [COLUMN] (col_name column_definition, ...): Adds multiple new columns simultaneously.
    • ADD {[INDEX | KEY] [index_name] [index_option] ...: Adds an index, specifying the index name and index options (such as 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.
    • 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.
    • RENAME [TO | AS] new_tbl_name: Renames the table.

  3. key_part: Represents the components of an index, which can be column names (when creating an index on a text column, you might specify a length for the index to only consider a certain number of characters in that column. If you create an index using a column name without specifying a length, the index will use the entire column value as an index component. In some cases, this may result in reduced performance, especially when dealing with large text or binary data columns. Specifying a length is usually unnecessary for smaller data types, such as integers or dates.).

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

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

Examples

  1. -- Create table f1 with two integer columns: fa (primary key) and fb (unique key)
  2. CREATE TABLE f1(fa INT PRIMARY KEY, fb INT UNIQUE KEY);
  3. -- Create table c1 with two integer columns: ca and cb
  4. CREATE TABLE c1 (ca INT, cb INT);
  5. -- Add a foreign key constraint named ffa to table c1, associating column ca of table c1 with column fa of table f1
  6. ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
  7. -- Insert a record into table f1: (2, 2)
  8. INSERT INTO f1 VALUES (2, 2);
  9. -- Insert a record into table c1: (1, 1)
  10. INSERT INTO c1 VALUES (1, 1);
  11. -- Insert a record into table c1: (2, 2)
  12. INSERT INTO c1 VALUES (2, 2);
  13. -- Select all records from table c1 and order by column ca
  14. mysql> select ca, cb from c1 order by ca;
  15. +------+------+
  16. | ca   | cb   |
  17. +------+------+
  18. |    2 |    2 |
  19. +------+------+
  20. 1 row in set (0.01 sec)
  21. -- Drop the foreign key constraint named ffa from table c1
  22. ALTER TABLE c1 DROP FOREIGN KEY ffa;
  23. -- Insert a record into table c1: (1, 1)
  24. INSERT INTO c1 VALUES (1, 1);
  25. -- Select all records from table c1 and order by column ca
  26. mysql> select ca, cb from c1 order by ca;
  27. +------+------+
  28. | ca   | cb   |
  29. +------+------+
  30. |    1 |    1 |
  31. |    2 |    2 |
  32. +------+------+
  33. 2 rows in set (0.01 sec)

Constraints

MatrixOne currently only supports the ALTER INDEX index_name {VISIBLE | INVISIBLE} syntax.