SQL Alter Table

ALTER TABLE operation performs on exists table, through ALTER TABLE command we can ADD COLUMN, UPDATE COLUMN, and DROP COLUMN.

ALTER TABLE is also used to add INDEX, drop INDEX, ADD CONSTRAINT and DROP CONSTRAINT like (ADD UNIQUE/DROP UNIQUE/ADD FOREIGN KEY/DROP FOREIGN KEY), etc.

Add COLUMN INTO TABLE


ALTER TABLE tableName ADD columnName datatype

If you want to add multiple columns into the table

ALTER TABLE tableName ADD (columnName1 datatype, columnName2 datatype, columnName3 datatype……columnNameN datatype)

Suppose We have an employees table



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+

Now Add column mobile_number into employees table


ALTER TABLE employees ADD mobile_number varchar(10)

Now, check employees table


+----+------------+-----------+----------------+------------+---------------+
| id | first_name | last_name | email          | address    | mobile_number |
+----+------------+-----------+----------------+------------+---------------+
|  1 | John       | Tailor    | john@abc.com   | California | NULL          |
|  2 | Rom        | Tailor    | rom@abc.com    | California | NULL          |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     | NULL          |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     | NULL          |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     | NULL          |
+----+------------+-----------+----------------+------------+---------------+

DROP COLUMN FROM TABLE


ALTER TABLE tableName DROP COLUMN columnName

Example:-
Now, we are going to drop mobile_number column from the employees table



ALTER TABLE employees DROP COLUMN mobile_number

Now, check employees table



+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email          | address    |
+----+------------+-----------+----------------+------------+
|  1 | John       | Tailor    | john@abc.com   | California |
|  2 | Rom        | Tailor    | rom@abc.com    | California |
|  3 | Andrew     | Symonds   | andrew@abc.com | Sydney     |
|  4 | Miacle     | clerk     | miacle@abc.com | sydney     |
|  5 | Sachin     | Tendulkar | sachin@abc.com | Mumbai     |
+----+------------+-----------+----------------+------------+