SQL Alter Table Syntax
SQL Alter Table Syntax modifies the existing table definition.
Understand with Example
The Tutorial illustrate an example from SQL Alter Table 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
