•With WITH VALIDATION , ALTER TABLE  copies the table. If an out-of-range or any other error occurs, the statement fails. Because a table copy is performed, the statement takes longer. 

WITHOUT VALIDATION  and WITH VALIDATION  are permitted only with ADD COLUMN , CHANGE COLUMN , and MODIFY COLUMN  operations. Otherwise, an ER_WRONG_USAGE  error occurs. 

•If expression evaluation causes truncation or provides incorrect input to a function, the ALTER TABLE statement terminates with an error and the DDL operation is rejected. 

•An ALTER TABLE  statement that changes the default value of a column col_name  may also change the value of a generated column expression that refers to the column using col_name , which may change the value of a generated column expression that refers to the column using DEFAULT( col_name ) . For this reason, ALTER TABLE  operations that change the definition of a column cause a table rebuild if any generated column expression uses DEFAULT() . 

## 13.1.9.3 ALTER TABLE Examples 

Begin with a table t1  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 an index on column d  and a UNIQUE  index on column a : 

ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (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); 

We indexed c  (as a PRIMARY KEY ) because AUTO_INCREMENT  columns must be indexed, and we declare c  as NOT NULL  because primary key columns cannot be NULL . 

For NDB  tables, it is also possible to change the storage type used for a table or column. For example, consider an NDB  table created as shown here: 

mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (1.27 sec) 

To convert this table to disk-based storage, you can use the following ALTER TABLE  statement: 

mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK; Query OK, 0 rows affected (2.99 sec) Records: 0 Duplicates: 0 Warnings: 0 

