|
| |
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 |
0 |
|
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 |
0 |
|
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 filed unique and then altering the field to add auto increment property to it. We will start with making it unique
Declaring an numeric filed to be unique
$q="ALTER TABLE `message_table` ADD UNIQUE (`msg_id`)";
Here in the message_table we already have one numeric filed 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.
| |
|
| HOME |
| SQL Tutorial List |
| SQL (Home) |
| SQL Commands |
|
|
|
|
|
| Subscribe |
|
Submit your email address and receive
article and product notifications. Your email is safe with us.
|
|
|
|
