MySQL alter table command


MySQL alter table command is used to make changes in the structure of any existing table. Alter table command is used to add, modify and drop column(s)
Alter table command can also be used to change the constraints of the columns-

Syntax of alter table command

ALTER TABLE tableName ADD/MODIFY (columnName datatype(size) Constraints, ColumnName datatype(size) CONSTRAINTS,.......);

ALTER TABLE tableName DROP(columnName, ColumnName,.......)

NOTE: We have two syntax for add/modify the existing column that is totally different from the DROP column

Demo DataBase Table for Applying alter Table command

mysql>desc student;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| admno       | char(10)     | NO   | PRI | NULL    |       |
| name        | char(30)     | NO   |     | NULL    |       |
| father_name | varchar(30)  | YES  |     | NULL    |       |
| address     | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Now we are going to add two more columns in this table of student marks int(5,2) and grade char(2)

mysql>alter table student add (marks float(5,2), grade char(2));
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| admno       | char(10)     | NO   | PRI | NULL    |       |
| name        | char(30)     | NO   |     | NULL    |       |
| father_name | varchar(30)  | YES  |     | NULL    |       |
| address     | varchar(100) | YES  |     | NULL    |       |
| marks       | float(5,2)   | YES  |     | NULL    |       |
| grade       | char(2)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Suppose we added one more column in the above table student phone with data type integer and size 10

mysql>alter table student add phone integer(10);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| admno       | char(10)     | NO   | PRI | NULL    |       |
| name        | char(30)     | NO   |     | NULL    |       |
| father_name | varchar(30)  | YES  |     | NULL    |       |
| address     | varchar(100) | YES  |     | NULL    |       |
| marks       | float(5,2)   | YES  |     | NULL    |       |
| grade       | char(2)      | YES  |     | NULL    |       |
| phone       | int(10)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Now realized that the datatype must be a character type value of size 15. The command to change the datatype and size is

mysql>alter table student modify phone char(15);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>desc student;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| admno       | char(10)     | NO   | PRI | NULL    |       |
| name        | char(30)     | NO   |     | NULL    |       |
| father_name | varchar(30)  | YES  |     | NULL    |       |
| address     | varchar(100) | YES  |     | NULL    |       |
| marks       | float(5,2)   | YES  |     | NULL    |       |
| grade       | char(2)      | YES  |     | NULL    |       |
| phone       | char(15)     | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Now the things look good but two columns that we do not want in this table are father_name and address. Drop these two columns using the alter table drop command

mysql>alter table student drop father_name;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| admno   | char(10)     | NO   | PRI | NULL    |       |
| name    | char(30)     | NO   |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| marks   | float(5,2)   | YES  |     | NULL    |       |
| grade   | char(2)      | YES  |     | NULL    |       |
| phone   | char(15)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Drop Primary Key

Primary key can be drop from any exisiting table using the following alter table command

mysql>alter table student drop primary key;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| admno   | char(10)     | NO   |     | NULL    |       |
| name    | char(30)     | NO   |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| marks   | float(5,2)   | YES  |     | NULL    |       |
| grade   | char(2)      | YES  |     | NULL    |       |
| phone   | char(15)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Add Primary key

Primary key can be added using the following command

mysql> alter table student add primary key(admno);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| admno   | char(10)     | NO   | PRI | NULL    |       |
| name    | char(30)     | NO   |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| marks   | float(5,2)   | YES  |     | NULL    |       |
| grade   | char(2)      | YES  |     | NULL    |       |
| phone   | char(15)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Change Column Name in MYSQL Table

mysql> alter table student change address stream char(20);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| admno  | char(10)   | NO   | PRI | NULL    |       |
| name   | char(30)   | NO   |     | NULL    |       |
| stream | char(20)   | YES  |     | NULL    |       |
| marks  | float(5,2) | YES  |     | NULL    |       |
| grade  | char(2)    | YES  |     | NULL    |       |
| phone  | char(15)   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Drop multiple column from MySQL Table

Alter table command can also drop multiple column from single alter table command but the syntax is little bit different.

Syntax
mysql>alter table  drop column_name, drop column_name, drop column_name; 
Example :
mysql > alter table student drop class, drop section, drop stream;

This way you can drop multiple columns with a single alter table command otherwise you have to pass multiple times alter table drop command.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.