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