SQL Alter Column Syntax

SQL Alter Column Syntax modifies the existing table definition.

Understand with Example

The Tutorial illustrate an example from SQL Alter Column Syntax. The list of Syntax performs the following operations:

Alter Table Name ADD [COLUMN] col_name column_definition[FIRST | AFTER col_name ] : The Query is used to position the field using FIRST and AFTER modifiers.
Alter Table Name | ADD [COLUMN] (col_name column_definition,): The Query is used to modify the existing table structure and modify the column in a table.

Alter Table Name | ADD {INDEX|KEY} [index_name]: The Query is used to modify the table name and add a index key on column of a table.

Alter Table Name | ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (index_col_name,...) [index_option] : The Query is used to change the existing table structure and add constraint on Primary Key.

Alter Table Name ADD FULLTEXT [INDEX|KEY] [index_name](index_col_name,...) [index_option]: The Query is used to modify the table and ADD keyword FULLTEXT clause adds an index on text column with an existing MyISAM.

Drop Table : The Query is used to remove and delete the table from database.

Drop Primary Key: The Query is used to remove or delete the Primary Key from the table.

REMOVE PARTITIONING :The Query is used to remove the partition function from the current database.

COALESCE PARTITION number : The Query is used to reduce the partition from the table without the loss of data.

Syntax for Alter

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    table_option ...
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | partition_options
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'