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