SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL

SQL ALTER Command

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 

Please not 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 Attributes Null Default 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 Attributes Null Default 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.

Number of User Comments : 5


Google+

tamil10-02-2009
This site looks good
nandy25-02-2010
Very useful and reference-friendly.
Dave26-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.
smo26-09-2010
These are SQL commands only. There is no PHP code here. Tested in phpmyadmin with MySQL
bhavik19-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...
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer