ALTER TABLE

Function

Modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.

Precautions

• This section describes only the new syntax of Dolphin. The original syntax of MogDB is not deleted or modified.
• If a statement contains multiple subcommands, the DROP INDEX and RENAME INDEX commands are executed first. The two commands have the same priority.

Syntax

• ALTER TABLE modifies the definition of a table.

  ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
      action [, ... ];

  The action can be one of the following clauses:

  column_clause
      | {DISABLE | ENABLE} KEYS
      | DROP INDEX index_name [ RESTRICT | CASCADE ]
      | DROP PRIMARY KEY [ RESTRICT | CASCADE ]
      | DROP FOREIGN KEY foreign_key_name [ RESTRICT | CASCADE ]
      | RENAME INDEX index_name to new_index_name
      | ADD table_indexclause
      | MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP

• Recreate a table.

  ALTER TABLE table_name FORCE;

• Rename a table. The renaming does not affect stored data.

  ALTER TABLE [ IF EXISTS ] table_name 
      RENAME { TO | AS } new_table_name;

• Add the ON UPDATE attribute to the timestamp column of the table.

  ALTER TABLE table_name
      MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP;

• Delete the ON UPDATE attribute from the timestamp column of the table.

  ALTER TABLE table_name
      MODIFY column_name column_type;

• ADD table_indexclause

  Add an index to the table.

  {INDEX | KEY} [index_name] [index_type] (key_part,...)[index_option]...

  Values of index_type are as follows:

  USING {BTREE | HASH | GIN | GIST | PSORT | UBTREE}

  Values of key_part are as follows:

  {col_name[(length)] | (expr)} [ASC | DESC]

  The index_option parameter is as follows:

  index_option:{
        COMMENT 'string'
      | index_type
  }

  The sequence and quantity of COMMENT and index_type can be random, but only the last value of the same column takes effect.

Parameter Description

• {DISABLE | ENABLE} KEYS

  Disables or enables all non-unique indexes of a table.

• DROP INDEX index_name [ RESTRICT | CASCADE ]

  Deletes the index of a table.

• DROP PRIMARY KEY [ RESTRICT | CASCADE ]

  Deletes the foreign key of a table.

• DROP FOREIGN KEY foreign_key_name [ RESTRICT | CASCADE ]

  Deletes the foreign key of a table.

• RENAME INDEX index_name to new_index_name

  Renames an index of a table.

NOTE:

For details about the involved parameters, see ALTER TABLE.

Examples

— Create tables, foreign keys, and non-unique indexes.

MogDB=# CREATE TABLE alter_table_tbl1 (a INT PRIMARY KEY, b INT);
MogDB=# CREATE TABLE alter_table_tbl2 (c INT PRIMARY KEY, d INT);
MogDB=# ALTER TABLE alter_table_tbl2 ADD CONSTRAINT alter_table_tbl_fk FOREIGN KEY (d) REFERENCES alter_table_tbl1 (a);
MogDB=# CREATE INDEX alter_table_tbl_b_ind ON alter_table_tbl1(b);

— Disable and enable non-unique indexes.

MogDB=# ALTER TABLE alter_table_tbl1 DISABLE KEYS;
MogDB=# ALTER TABLE alter_table_tbl1 ENABLE KEYS;

— Delete the index.

MogDB=# ALTER TABLE alter_table_tbl1 DROP KEY alter_table_tbl_b_ind;

— Deletes a primary key.

MogDB=# ALTER TABLE alter_table_tbl2 DROP PRIMARY KEY;

— Delete a foreign key.

MogDB=# ALTER TABLE alter_table_tbl2 DROP FOREIGN KEY alter_table_tbl_fk;

— Recreate a table.

MogDB=# ALTER TABLE alter_table_tbl1 FORCE;

— Rename the index.

MogDB=# CREATE INDEX alter_table_tbl_b_ind ON alter_table_tbl1(b);
MogDB=# ALTER TABLE alter_table_tbl1 RENAME INDEX alter_table_tbl_b_ind TO new_alter_table_tbl_b_ind;

— Delete a table.

MogDB=# DROP TABLE alter_table_tbl1, alter_table_tbl2;

Helpful Links

ALTER TABLE