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'


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


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 Constraint

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


* indicates required
Subscribe to plus2net



    This site looks good


    Very useful and reference-friendly.


    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.


    These are SQL commands only. There is no PHP code here. Tested in phpmyadmin with MySQL


    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...

    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer