SQL ALTER commands to change the structure of a MySQL table

Table structure can be changed by using alter command. With this command Field type or property can be changed or a new field can be added. This sql alter table command is used like create table command.

Adding column

ALTER TABLE  `student`  ADD  `rank` INT( 5 ) NULL

Without any null value ( 0 will be stored in rank column)

ALTER TABLE  `student` ADD  `rank` INT( 3 ) NOT NULL

With default value

ALTER TABLE  `student` ADD  `rank` INT( 3 ) NOT NULL DEFAULT  '10'

Using AFTER

ALTER TABLE  `student` ADD  `last_name` VARCHAR( 50 ) NOT NULL AFTER  `name`

Using CHANGE

ALTER TABLE  `student` CHANGE  `last_name`  `last_name` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

Deleting Column

ALTER TABLE  `student` DROP  `last_name` ;

Please note that while changing the structure we must honor the existing constraints of the table. For example if you decide to change a field to UNIQUE so it will not accept any duplicate records then if some records with duplicate value are already there then system will not allow this and we will get error message.
Same way we have to take care of other constraints

Here is the existing table structure.

Field	Type	Null	Extra
id	int(2)	No	auto_increment
name	varchar(50)	No
class	varchar(10)	No
mark	int(3)	No

We will apply our alter table command to this table. We will change field name mark to student_mark Here is the command

ALTER  TABLE `student3`  CHANGE `mark`  `student_mark` INT(  3 )  DEFAULT '0'  NOT NULL

With this alter table command the the field name mark will change to student_mark. This way we are changing a field name only. Same way field type, default value and other properties of the field can be changed. The new table structure is listed below.

Field	Type	Null	Extra
id	int(2)	No	auto_increment
name	varchar(50)	No
class	varchar(10)	No
student_mark	int(3)	No

Adding / Altering a numeric field to auto increment field

You can read purpose and how to create auto increment field here. We can convert one existing numeric field to an auto increment field in two steps. First making the field unique and then altering the field to add auto increment property to it. We will start with making it unique

Declaring an numeric field to be unique

$q="ALTER TABLE `message_table` ADD UNIQUE (`msg_id`)";

Here in the message_table we already have one numeric field msg_id and we have made it to UNIQUE field so no duplicate data is allowed, if any duplicate data is there then we will receive error message.

Now let us change it to add auto increment property to it.

$q="ALTER TABLE `message_table` CHANGE `msg_id` `msg_id` INT( 4 ) NOT NULL AUTO_INCREMENT ";

Now our msg_id field became auto increment.

Primary Key is unique and not null constraint of the column to identify the row and we can use one Primary Key for one table.

Primary Key constraint

SQL References Create Table

Subscribe to our YouTube Channel here

Dave	26-09-2010
I noticed that the code to make a field unique and auto_increment is PHP code, not a direct SQL command, while the code for changing the name of a field is straight SQL code. I am inexperienced in PHP however I can use SQL commands fine. Please tell me the equivalent SQL code for making a field unique and auto_increment. Removing the quotes, parens and variable names does not seem to work.

smo	26-09-2010
These are SQL commands only. There is no PHP code here. Tested in phpmyadmin with MySQL

bhavik	19-03-2011
i have 1 question. first we create table and define two column id and name. but that time we missed to define id as auto_increment, now what we do. i want solved this problem with query. can we do that with ATLER TABLE ? please give me the answer sir...

tamil	10-02-2009
This site looks good

nandy	25-02-2010
Very useful and reference-friendly.

SQL ALTER Command