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'
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
