MySQL社区

标题: ALTER TABLE语法 [打印本页]

作者: admin 时间: 2007-7-20 18:13
标题: ALTER TABLE语法
ALTER TABLE语法
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| 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 column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| 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

ALTER TABLE用于更改原有表的结构。例如，您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型。

作者: infoyj 时间: 2007-10-25 12:28
标题: 如何取消对列的约束条件, 如 UNIQUE
某一列的约束条件是UNIQUE, 如何消除它?

ALTER TABLE tableName DROP UNIQUE columnName;
不能被执行

作者: kider 时间: 2007-10-25 17:34
UNIQUE 属于索引范围，应该是删除IndexName就对了...

作者: infoyj 时间: 2007-10-26 11:26
谢啦
用DROP KEY column_name 就可以了

作者: mysqlkumao 时间: 2008-4-10 10:24
应该是 alter table TB_Name drop index Index_Name;

index与key同意
在创建key时，如果不给出Index_Name，MySQL自动用列名指定

欢迎光临 MySQL社区 (http://www.mysqlpub.com/)