ALTER [IGNORE] TABLEtbl_name
alter_specification
[,alter_specification
] ...alter_specification
: ADD [COLUMN]column_definition
[FIRST | AFTERcol_name
] | ADD [COLUMN] (column_definition
,...) | ADD {INDEX|KEY} [index_name
] [index_type
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] PRIMARY KEY [index_type
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] UNIQUE [INDEX|KEY] [index_name
] [index_type
] (index_col_name
,...) | ADD FULLTEXT [INDEX|KEY] [index_name
] (index_col_name
,...) [WITH PARSERparser_name
] | ADD SPATIAL [INDEX|KEY] [index_name
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
,...) [reference_definition
] | ALTER [COLUMN]col_name
{SET DEFAULTliteral
| DROP DEFAULT} | CHANGE [COLUMN]old_col_name
column_definition
[FIRST|AFTERcol_name
] | MODIFY [COLUMN]column_definition
[FIRST | AFTERcol_name
] | DROP [COLUMN]col_name
| DROP PRIMARY KEY | DROP {INDEX|KEY}index_name
| DROP FOREIGN KEYfk_symbol
| DISABLE KEYS | ENABLE KEYS | RENAME [TO]new_tbl_name
| ORDER BYcol_name
| CONVERT TO CHARACTER SETcharset_name
[COLLATEcollation_name
] | [DEFAULT] CHARACTER SETcharset_name
[COLLATEcollation_name
] | DISCARD TABLESPACE | IMPORT TABLESPACE |table_option
... |partition_options
| ADD PARTITION (partition_definition
) | DROP PARTITIONpartition_names
| COALESCE PARTITIONnumber
| REORGANIZE PARTITIONpartition_names
INTO (partition_definitions
) | ANALYZE PARTITIONpartition_names
| CHECK PARTITIONpartition_names
| OPTIMIZE PARTITIONpartition_names
| REBUILD PARTITIONpartition_names
| REPAIR PARTITIONpartition_names
| REMOVE PARTITIONING
ALTER TABLE
enables you to change the
structure of an existing table. For example, you can add or
delete columns, create or destroy indexes, change the type of
existing columns, or rename columns or the table itself. You can
also change the comment for the table and type of the table.
The syntax for many of the allowable alterations is similar to
clauses of the CREATE TABLE
statement. This
includes table_option
modifications,
for options such as ENGINE
,
AUTO_INCREMENT
, and
AVG_ROW_LENGTH
. (However, ALTER
TABLE
ignores the DATA DIRECTORY
and INDEX DIRECTORY
table options.)
Section 13.1.7, “CREATE TABLE
Syntax”, lists all table options. As of
MySQL 5.1.11, to prevent inadvertant loss of data,
ALTER TABLE
cannot be used to change the
storage engine of a table to MERGE
or
BLACKHOLE
.
Some operations may result in warnings if attempted on a table
for which the storage engine does not support the operation.
These warnings can be displayed with SHOW
WARNINGS
. See Section 13.5.4.30, “SHOW WARNINGS
Syntax”.
In most cases, ALTER TABLE
works by making a
temporary copy of the original table. The alteration is
performed on the copy, and then the original table is deleted
and the new one is renamed. While ALTER TABLE
is executing, the original table is readable by other clients.
Updates and writes to the table are stalled until the new table
is ready, and then are automatically redirected to the new table
without any failed updates.
In some cases, no temporary table is necessary:
If you use ALTER TABLE
without
any other options, MySQL simply renames any files that
correspond to the table tbl_name
RENAME TO
new_tbl_name
tbl_name
.
(You can also use the RENAME TABLE
statement to rename tables. See
Section 13.1.16, “RENAME TABLE
Syntax”.)
ALTER TABLE ... ADD PARTITION
creates no
temporary table except for MySQL Cluster.
ADD
or DROP
operations
for RANGE
or LIST
partitions are immediate operations or nearly so.
ADD
or COALESCE
operations for HASH
or
KEY
partitions copy data between changed
partitions; unless LINEAR HASH/KEY
was
used, this is much the same as creating a new table
(although the operation is done partition by partition).
REORGANIZE
operations copy only changed
partitions and do not touch unchanged ones.
If other cases, MySQL creates a temporary table, even if the
data wouldn't strictly need to be copied (such as when you
change the name of a column). For MyISAM
tables, you can speed up the index re-creation operation (which
is the slowest part of the alteration process) by setting the
myisam_sort_buffer_size
system variable to a
high value.
To use ALTER TABLE
, you need
ALTER
, INSERT
, and
CREATE
privileges for the table.
IGNORE
is a MySQL extension to standard
SQL. It controls how ALTER TABLE
works if
there are duplicates on unique keys in the new table or if
warnings occur when strict mode is enabled. If
IGNORE
is not specified, the copy is
aborted and rolled back if duplicate-key errors occur. If
IGNORE
is specified, only the first row
is used of rows with duplicates on a unique key, The other
conflicting rows are deleted. Incorrect values are truncated
to the closest matching acceptable value.
You can issue multiple ADD
,
ALTER
, DROP
, and
CHANGE
clauses in a single ALTER
TABLE
statement, separated by commas. This is a
MySQL extension to standard SQL, which allows only one of
each clause per ALTER TABLE
statement.
For example, to drop multiple columns in a single statement,
do this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
CHANGE
, col_name
DROP
, and
col_name
DROP INDEX
are MySQL extensions to
standard SQL.
MODIFY
is an Oracle extension to
ALTER TABLE
.
The word COLUMN
is optional and can be
omitted.
column_definition
clauses use the
same syntax for ADD
and
CHANGE
as for CREATE
TABLE
. Note that this syntax includes the column
name, not just its data type. See
Section 13.1.7, “CREATE TABLE
Syntax”.
You can rename a column using a CHANGE
clause. To do so, specify the old and new column names and
the type that the column currently has. For example, to
rename an old_col_name
column_definition
INTEGER
column from
a
to b
, you can do
this:
ALTER TABLE t1 CHANGE a b INTEGER;
If you want to change a column's type but not the name,
CHANGE
syntax still requires an old and
new column name, even if they are the same. For example:
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
You can also use MODIFY
to change a
column's type without renaming it:
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
If you use CHANGE
or
MODIFY
to shorten a column for which an
index exists on the column, and the resulting column length
is less than the index length, MySQL shortens the index
automatically.
When you change a data type using CHANGE
or MODIFY
, MySQL tries to convert
existing column values to the new type as well as possible.
To add a column at a specific position within a table row,
use FIRST
or AFTER
. The default
is to add the column last. You can also use
col_name
FIRST
and AFTER
in
CHANGE
or MODIFY
operations.
ALTER ... SET DEFAULT
or ALTER
... DROP DEFAULT
specify a new default value for a
column or remove the old default value, respectively. If the
old default is removed and the column can be
NULL
, the new default is
NULL
. If the column cannot be
NULL
, MySQL assigns a default value, as
described in Section 11.1.4, “Data Type Default Values”.
DROP INDEX
removes an index. This is a
MySQL extension to standard SQL. See
Section 13.1.11, “DROP INDEX
Syntax”.
If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well.
If a table contains only one column, the column cannot be
dropped. If what you intend is to remove the table, use
DROP TABLE
instead.
DROP PRIMARY KEY
drops the primary index.
Note: In older versions of MySQL, if no
primary index existed, DROP PRIMARY KEY
would drop the first UNIQUE
index in the
table. This is not the case in MySQL 5.1, where
trying to use DROP PRIMARY KEY
on a table
with no primary key give rises to an error.
If you add a UNIQUE INDEX
or
PRIMARY KEY
to a table, it is stored
before any non-unique index so that MySQL can detect
duplicate keys as early as possible.
ORDER BY
enables you to create the new
table with the rows in a specific order. Note that the table
does not remain in this order after inserts and deletes.
This option is useful primarily when you know that you are
mostly to query the rows in a certain order most of the
time. By using this option after major changes to the table,
you might be able to get higher performance. In some cases,
it might make sorting easier for MySQL if the table is in
order by the column that you want to order it by later.
If you use ALTER TABLE
on a
MyISAM
table, all non-unique indexes are
created in a separate batch (as for REPAIR
TABLE
). This should make ALTER
TABLE
much faster when you have many indexes.
This feature can be activated explicitly. ALTER
TABLE ... DISABLE KEYS
tells MySQL to stop
updating non-unique indexes for a MyISAM
table. ALTER TABLE ... ENABLE KEYS
then
should be used to re-create missing indexes. MySQL does this
with a special algorithm that is much faster than inserting
keys one by one, so disabling keys before performing bulk
insert operations should give a considerable speedup. Using
ALTER TABLE ... DISABLE KEYS
requires the
INDEX
privilege in addition to the
privileges mentioned earlier.
ENABLE KEYS
and DISABLE
KEYS
were not supported for partitioned tables
prior to MySQL 5.1.11. (Bug#19502)
The FOREIGN KEY
and
REFERENCES
clauses are supported by the
InnoDB
storage engine, which implements
ADD [CONSTRAINT
[
. See
Section 15.2.6.4, “symbol
]] FOREIGN KEY (...)
REFERENCES ... (...)FOREIGN KEY
Constraints”. For other
storage engines, the clauses are parsed but ignored. The
CHECK
clause is parsed but ignored by all
storage engines. See Section 13.1.7, “CREATE TABLE
Syntax”. The
reason for accepting but ignoring syntax clauses is for
compatibility, to make it easier to port code from other SQL
servers, and to run applications that create tables with
references. See Section 1.9.5, “MySQL Differences from Standard SQL”.
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER TABLE
statement. You must use separate statements.
InnoDB
supports the use of ALTER
TABLE
to drop foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER TABLE
statement. You must use separate statements.
For more information, see
Section 15.2.6.4, “FOREIGN KEY
Constraints”.
If you want to change the table default character set and
all character columns (CHAR
,
VARCHAR
, TEXT
) to a
new character set, use a statement like this:
ALTER TABLEtbl_name
CONVERT TO CHARACTER SETcharset_name
;
Warning: The preceding
operation converts column values between the character sets.
This is not what you want if you have a
column in one character set (like latin1
)
but the stored values actually use some other, incompatible
character set (like utf8
). In this case,
you have to do the following for each such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The reason this works is that there is no conversion when
you convert to or from BLOB
columns.
If you specify CONVERT TO CHARACTER SET
binary
, the CHAR
,
VARCHAR
, and TEXT
columns are converted to their corresponding binary string
types (BINARY
,
VARBINARY
, BLOB
). This
means that the columns no longer will have a character set
and a subsequent CONVERT TO
operation
will not apply to them.
To change only the default character set for a table, use this statement:
ALTER TABLEtbl_name
DEFAULT CHARACTER SETcharset_name
;
The word DEFAULT
is optional. The default
character set is the character set that is used if you do
not specify the character set for a new column which you add
to a table (for example, with ALTER TABLE ... ADD
column
).
For an InnoDB
table that is created with
its own tablespace in an .ibd
file,
that file can be discarded and imported. To discard the
.ibd
file, use this statement:
ALTER TABLE tbl_name
DISCARD TABLESPACE;
This deletes the current .ibd
file, so
be sure that you have a backup first. Attempting to access
the table while the tablespace file is discarded results in
an error.
To import the backup .ibd
file back
into the table, copy it into the database directory, and
then issue this statement:
ALTER TABLE tbl_name
IMPORT TABLESPACE;
A number of partitioning-related extensions to
ALTER TABLE
were added in MySQL 5.1.5.
These can be used with partitioned tables for
repartitioning, for adding, dropping, merging, and splitting
partitions, and for performing partitioning maintenance.
The partition_definition
clause
for ALTER TABLE ADD PARTITION
supports
the same options as the clause of the same name does for the
CREATE TABLE
statement clause of the same
name. (See Section 13.1.7, “CREATE TABLE
Syntax”, for the syntax
and description.) Suppose that you have the partitioned
table created as shown here:
CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) );
You can add a new partition p3
to this
table for storing values less then 2002
as follows:
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
Note: You cannot use
ALTER TABLE
to add partitions to a table
that is not already partitioned.
DROP PARTITION
can be used to drop one or
more RANGE
or LIST
partitions. This statement cannot be used with
HASH
or KEY
partitions; instead, use COALESCE
PARTITION
(see below). Any data that was stored in
the dropped partitions named in the
partition_names
list is
discarded. For example, given the table
t1
defined previously, you can drop the
partitions named p0
and
p1
as shown here:
ALTER TABLE t1 DROP PARTITION p0, p1;
Note that DROP PARTITION
does not work
with tables that use the NDB Cluster
storage engine. See
Section 18.3.1, “Management of RANGE
and LIST
Partitions”, and
Section 17.11, “Known Limitations of MySQL Cluster”.
ADD PARTITION
and DROP
PARTITION
do not currently support IF
[NOT] EXISTS
. It is also not possible to rename a
partition or a partitioned table. Instead, if you wish to
rename a partition, you must drop and re-create the
partition; if you wish to rename a partitioned table, you
must instead drop all partitions, rename the table, and then
add back the partitions that were dropped.
COALESCE PARTITION
can be used with a
table that is partitioned by HASH
or
KEY
to reduce the number of partitions by
number
. Suppose that you have
created table t2
using the following
definition:
CREATE TABLE t2 ( name VARCHAR (30), started DATE ) PARTITION BY HASH( YEAR(started) ) PARTITIONS 6;
You can reduce the number of partitions used by
t2
from 6 to 4 using the following
statement:
ALTER TABLE t2 COALESCE PARTITION 2;
The data contained in the last
number
partitions will be merged
into the remaining partitions. In this case, partitions 4
and 5 will be merged into the first 4 partitions (the
partitions numbered 0, 1, 2, and 3.
To change some but not all the partitions used by a
partitioned table, you can use REORGANIZE
PARTITION
. This statement can be used in several
ways:
To merge a set of partitions into a single partition.
This can be done by naming several partitions in the
partition_names
list and
supplying a single definition for
partition_definition
.
To split an existing partition into several partitions.
You can accomplish this by naming a single partition for
partition_names
and providing
multiple
partition_definitions
.
To change the ranges for a subset of partitions defined
using VALUES LESS THAN
or the value
lists for a subset of partitions defined using
VALUES IN
.
Note: For partitions that
have not been explicitly named, MySQL automatically provides
the default names p0
,
p1
, p2
, and so on. As
of MySQL 5.1.7, the same is true with regard to
subpartitions.
For more detailed information about and examples of
ALTER TABLE ... REORGANIZE PARTITION
statements, see Section 18.3, “Partition Management”.
Several additional clauses provide partition maintenance and
repair functionality analogous to that implemented for
non-partitioned tables by statements such as CHECK
TABLE
and REPAIR TABLE
(which
are not supported for partitioned
tables). These include ANALYZE PARTITION
,
CHECK PARTITION
, OPTIMIZE
PARTITION
, REBUILD PARTITION
,
and REPAIR PARTITION
. Each of these
options takes a partition_names
clause consisting of one or more names of partitions,
separated by commas. The partitions must already exist in
the table to be altered. For more information, and for
examples of these, see
Section 18.3.3, “Maintenance of Partitions”.
REMOVE PARTITIONING
was introduced in
MySQL 5.1.8 for the purpose of removing a table's
partitioning without otherwise affecting the table or its
data. (Previously. this was done using the ENGINE
option.) This option can be combined with other
ALTER TABLE
options such as those used to
add, drop, or rename drop columns or indexes.
In MySQL 5.1.7 and earlier, using the
ENGINE
option with ALTER
TABLE
caused any partitioning that a table might
have had to be removed. Beginning with MySQL 5.1.8, this
option merely changes the storage engine used by the table
and no longer affects partitioning in any way.
With the mysql_info()
C API function, you can
find out how many rows were copied, and (when
IGNORE
is used) how many rows were deleted
due to duplication of unique key values. See
Section 26.2.3.34, “mysql_info()
”.
Here are some examples that show uses of ALTER
TABLE
. Begin with a table t1
that
is created as shown here:
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1
to
t2
:
ALTER TABLE t1 RENAME t2;
To change column a
from
INTEGER
to TINYINT NOT
NULL
(leaving the name the same), and to change column
b
from CHAR(10)
to
CHAR(20)
as well as renaming it from
b
to c
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP
column named
d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add indexes on column d
and on column
a
:
ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
To remove column c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT
integer column
named c
:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);
Note that we indexed c
(as a PRIMARY
KEY
), because AUTO_INCREMENT
columns must be indexed, and also that we declare
c
as NOT NULL
, because
primary key columns cannot be NULL
.
When you add an AUTO_INCREMENT
column, column
values are filled in with sequence numbers for you
automatically. For MyISAM
tables, you can set
the first sequence number by executing SET
INSERT_ID=
before
value
ALTER TABLE
or by using the
AUTO_INCREMENT=
table option. See Section 13.5.3, “value
SET
Syntax”.
You can use the ALTER TABLE ...
AUTO_INCREMENT=
table
option for value
InnoDB
tables to set the sequence
number for new rows if the value is greater than the maximum
value in the AUTO_INCREMENT
column.
If the value is less than the current maximum value in
the column, no error message is given and the current sequence
value is not changed.
With MyISAM
tables, if you do not change the
AUTO_INCREMENT
column, the sequence number is
not affected. If you drop an AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers are resequenced beginning with 1.
When replication is used, adding an
AUTO_INCREMENT
column to a table might not
produce the same ordering of the rows on the slave and the
master. This occurs because the order in which the rows are
numbered depends on the specific storage engine used for the
table and the order in which the rows were inserted. If it is
important to have the same order on the master and slave, the
rows must be ordered before assigning an
AUTO_INCREMENT
number. Assuming that you want
to add an AUTO_INCREMENT
column to the table
t1
, the following statements produce a new
table t2
identical to t1
but with an AUTO_INCREMENT
column:
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY) SELECT * FROM t1 ORDER BY col1, col2;
This assumes that the table t1
has columns
col1
and col2
.
This set of statements will also produce a new table
t2
identical to t1
, with
the addition of an AUTO_INCREMENT
column:
CREATE TABLE t2 LIKE t1; ALTER TABLE T2 ADD id INT AUTO_INCREMENT PRIMARY KEY; INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
Important: To guarantee the
same ordering on both master and slave, all
columns of t1
must be referenced in the
ORDER BY
clause.
Regardless of the method used to create and populate the copy
having the AUTO_INCREMENT
column, the final
step is to drop the original table and then rename the copy:
DROP t1; ALTER TABLE t2 RENAME t1;
User Comments
可以不需要修改my.ini。在建立数据库的时候,对库和表的字符集设置不太重要,但是对文本类型的字段最好都设置为GBK字符集。
对于已有的数据库可以用以下方法转换字段编码:
ALTER TABLE t MODIFY hoverfly BINARY(100);
ALTER TABLE t MODIFY hoverfly CHAR(100) CHARACTER SET gbk;
注意用此方法如果不修改程序,会发现查询出的结果都是乱码,可以通过在my.ini的[MySQLd]段内加default-character-set=gbk来纠正。但是这样你会发现那些没有转换编码的文本字段里的中文都是乱码。
其实有更简单的办法,在进行查询前,只要执行SET character_set_results = NULL就可以。而且这是不管是转换了的还是没转换的字段都不会出现乱码。而转换了的字段可以正常的对中文进行排序。
IF you want to change a SET or ENUM column you may
not want to use the ALTER TABLE ... MODIFY
syntax.
It tries to keep the actual string values and not
the integer representation of the values, even
though they are stored as integers.
For example, if you just want to make a change in
spelling of the values in your enum column or your
set column, consider doing it like this:
ALTER TABLE table ADD new_column ...;
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
You can use Alter Table to optimise a table without locking out selects (only writes), by altering a column to be the same as it's current definition. This is better than using repair table which obtains a read/write lock.
1 row in set (0.00 sec)E.g.
mysql> describe Temp_Table;
mysql> alter table Temp_Table change column ID ID int unsigned;
This will cause mysql to re-create the table and thus remove any deleted space.
This is useful for 24/7 databases where you don't want to completely lock a table.
while adding primary key constraint on null allowable integer column, mysql assigns default value 0 iff one NULL value exists on that column else alter table will fail with error "Duplicate entry '0' for key 1"
If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me):
For peace-of-mind -- try this with some dummy data first!
1. Backup the <original_table>.frm file from your master table (and the data if you can, but you're probably reading this because you can't).
2. create table with the identical schema to the one you want to alter (type "show create table <tablename> and just change the name to something). Lets say you called the table "rename_temp1"
3. execute the "alter table <rename_temp1> change <old_column_name> <new_column_name> char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]
3. Ensuring you a have made a copy of your original .frm file -- copy the <rename_temp1>.frm file to <original_table>.frm.
4. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)
5. probably best to run a myisamchck on the table before making live again
Simple example to add a field to a table
3 rows in set (0.00 sec)mysql> alter table cdrom add Genere varchar(250);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> describe cdrom;
When trying to change the type of the primary key field, do not specify the "primary key" attribute, or the alter table will fail, eg. if "foo" is the primary key:
mysql> alter table bar change foo foo mediumint unsigned not null auto_increment;
will work, but
mysql> alter table bar change foo foo mediumint unsigned not null auto_increment primary key;
will fail. This is true for 4.0.18, YMMV.
To reorder the columns of your table, you can use the ALTER TABLE syntax, since as of MySQL 4.0.1, the keywords FIRST and AFTER can be used in a CHANGE or MODIFY command.
3 rows in set (0.00 sec)Example:
mysql> describe example_table;
mysql> ALTER TABLE example_table CHANGE COLUMN columnB columnB TEXT NOT NULL AFTER id;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> describe example_table;
3 rows in set (0.00 sec)
When you want to drop a UNIQUE KEY in an InnoDb table, have to pay attention not to occure this situation:
Please check that columns used in the UNIQUE KEY are not used as FOREIGN KEY (each of them).
If so, must to drop that Forign keys first.
See Example below please.
UNIQUE KEY `unique` (`id1`, `id2`),
CONSTRAINT `fk_1` FOREIGN KEY (`id1`) REFERENCES `tbl1` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_2` FOREIGN KEY (`id2`) REFERENCES `tbl2` (`id`) ON DELETE CASCADE
In this situation, you have to drop both FOREIGN KEYs first, in order to can drop the UNIQUE KEY.
A few practical examples
- Adding a field with a default value
alter table comments add column test varchar(255) Default 'hello';
- Droping (removing) a field
alter table comments drop column test;
- adding a unique index
alter table comments add UNIQUE index_name (id, user,test);
If you encounter error 1064 when using table name or column name, just put backquotes (`) around name's like this:
alter table `table_name` modify `col_name` smallint(4);
ALTER TABLE ADD FOREIGN KEY defines [reference definition] as optional yet I believe this part should be required for this definition.
In addition, there is syntax definition on the page for [reference definition]
If you're trying to convert a whole database to a different character set, and you thought you might have to change the fields one by one, this kind of command is really handy:
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
However, after using it on a lot of tables I made the grim discovery that for older myisam tables that didn't have any character set, it mangled the length of most varchar fields. Specifically, it divided their length with 3. Bizarrely, it didn't lose the existing data, even though it was longer than the field lengths, but it was a problem with new records and with indexes.
So, if you're going to do a character set converstion, make sure the table already has a character set. That it doesn't might not be immediately obvious, as tools like phpMyAdmin will show the default character set, if the table or the field doesn't have one set.
TO ADD A FOREIGN KEY TO AN EXISTING TABLE (I couldn't see a good example) you can do this:
alter table users add foreign key(favGenre) references products_genre(gid);
Where favgenre is the column of the table that has the foreign key and products_genre(gid) is the table and primary key you are referencing.
To remove a false Primary Key and to set an new one, do the following:
ALTER TABLE `table` DROP PRIMARY KEY, ADD PRIMARY KEY (field1,field2,field3);
TO ADD A TABLE COMMENT do the following:
ALTER TABLE `mytable` COMMENT = 'my comment - this is a cool table';
To drop an unwanted UNIQUE KEY.
Use SHOW CREATE TABLE example; to locate the keyID.
Run ALTER TABLE example DROP KEY 'keyID';
Dropping of UNIQUE KEYS is not currently documented, so exact syntax may vary in future. This is tested for v5.0.11 to v5.0.15
Attempting to "ALTER TABLE ... DROP PRIMARY KEY" on a table when an AUTO_INCREMENT column exists in the key generates an error:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.
To make this work without erroring, drop and re-add the new primary key in a single statement, e.g.:
ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);