Column Properties

Column Properties
• Set a default value for a column: ALTER TABLE tbl ALTER COLUMN col SET DEFAULT
literal or ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT
The default values for columns are stored in the .frm file for the table, not the InnoDB data
dictionary.
• Changing the auto-increment value for a column: ALTER TABLE table
AUTO_INCREMENT=next_value;
Especially in a distributed system using replication or sharding, you sometimes reset the autoincrement
counter for a table to a specific value. The next row inserted into the table uses
the specified value for its auto-increment column. You might also use this technique in a data
warehousing environment where you periodically empty all the tables and reload them, and you can
restart the auto-increment sequence from 1.
• Renaming a column: ALTER TABLE tbl CHANGE old_col_name new_col_name datatype
When you keep the same data type and [NOT] NULL attribute, only changing the column name, this
operation can always be performed online.
As part of this enhancement, you can now rename a column that is part of a foreign key constraint,
which was not allowed before. The foreign key definition is automatically updated to use the new
column name. Renaming a column participating in a foreign key only works with the in-place mode
of ALTER TABLE. If you use the ALGORITHM=COPY clause, or some other condition causes the
command to use ALGORITHM=COPY behind the scenes, the ALTER TABLE statement will fail.
... zobacz całą notatkę