MySQL Alter Command

MySQL Alter Command is used to change the structure of existing table . we can use Alter Command add or drop column, change column attribute, add primary key, rename table,etc.

MySQL ALTER TABLE Syntax

ALTER TABLE tablename action1 [,action2 ,...]

Specification

  • First we need to specify the name of table that we want to change after ALTER TABLE.
  • Second, we need to list a set of actions that we want to apply to the table such as adding a new column,adding primary key, renaming table,changing column attribute, etc.

Examples

To add a column in a table.

ALTER TABLE example ADD employee_id INT(11) NOT NULL AUTO_INCREMENT;

To delete a column in a table.

ALTER TABLE example DROP COLUMN employee_name

To change the data type of a column in a table.

ALTER TABLE example CHANGE COLUMN employee_name employee_name INT(11) NOT NULL

To drop a column from table.

ALTER TABLE example DROP COLUMN employee_name

To rename a column in a table.

ALTER TABLE example CHANGE COLUMN employee_name employeename varchar(20) NOT NULL;

To rename table.

ALTER TABLE example RENAME TO exampledesc;

To add a new TIMESTAMP column in table.

ALTER TABLE example ADD employee_attendance TIMESTAMP ;

To add an index on column emp_first_name and UNIQUE index on column emp_last_name.

ALTER TABLE example ADD INDEX (emp_first_name), ADD UNIQUE (emp_last_name);

To add PRIMARY KEY in a column .

ALTER TABLE example ADD PRIMARY KEY(employee_id)

ALTER vs CHANGE vs MODIFY COLUMN

ALTER COLUMN

ALTER COLUMN is used to set or remove the default value for a column.

ALTER TABLE example ALTER COLUMN employee_name SET DEFAULT 'Manish'; ALTER TABLE example ALTER COLUMN employee_name DROP DEFAULT; CHANGE COLUMN

CHANGE COLUMN is used to rename a column, change its datatype, or move it within the schema.

ALTER TABLE example CHANGE COLUMN employee_name employee_name INT(11) NOT NULL; MODIFY COLUMN

MODIFY COLUMN used to do everything CHANGE COLUMN can, but without renaming the column.

ALTER TABLE example MODIFY COLUMN employee_name employee_name INT(11) NOT NULL;

Here is a table reference for ALTER vs CHANGE vs MODIFY COLUMN.

Method DROP Default Change Default Rename Column Change Datatype
ALTER Yes Yes NO No
CHANGE NO Yes Yes Yes
MODIFY NO Yes NO Yes