How to use the Alter Table Statement in a SQL Database

Once we have created a table in our database we can use alter statement to modify it. ALTER TABLE statement is used to add, delete, or modify columns in an existing table. Syntax : 

ALTER TABLE table_name ADD (column name) data type;
ALTER TABLE commands we can perform
  • 1-ALTER TABLE Add Column
  • 2-ALTER TABLE Drop Column
  • 3-ALTER TABLE Modify Column
  • 4-ALTER TABLE Rename Column
  • 5-ALTER TABLE Add Index
  • 6-ALTER TABLE Drop Index
  • 7-ALTER TABLE Add Constraint
  • 8-ALTER TABLE Drop Constraint
So let's use our old TEST table script to show some examples of alter table : Script: 
--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');
Check the table to see the data and the structure. 
SQL
select * from test ;
    ID NAME         EMAIL            SALARY  DE
 ---------- -------------------- -------------------- ---------- --  --------
     1  Eve         [email protected]        1500   HR
     2  Jon         [email protected]        2500   AD
     3  Mike        [email protected]       3000   AD
     4  Paul        [email protected]       3200   HR
     5  Mary        [email protected]       1800   IT
     6  Jane        [email protected]       2200   IT
Very good

1 -ALTER TABLE Add Column

Syntax: 
ALTER table_name add (column name) data type;
  • this is used when we want to add a column to our table .Let's add a column called AGE to our TEST table .
Example: -so the AGE column should be int . 
SQL
alter table test add AGE int;
-and there you go , I have just added a column to our TEST table . Now check the table : 
SQLselect * from test ;
    ID NAME         EMAIL           SALARY  DE     AGE
 ---------- -------------------- -------------------- ---------- -------- ---------
     1  Eve         [email protected]       1500   HR
     2  Jon         [email protected]       2500   AD
     3  Mike       [email protected]       3000   AD
     4  Paul        [email protected]       3200   HR
     5  Mary       [email protected]       1800   IT
     6  Jane        [email protected]      2200   IT
-it has no values we can see that the column was added .

2- ALTER TABLE Drop Column

  • using the same table now we will drop the column we have just added (AGE column).
Sintax: 
ALTER table_name drop column (column name);
Example: 
SQLalter table test drop column age;
Nice.Check the table 
SQLselect * from test ;    ID NAME         EMAIL            SALARY  DE
---------- -------------------- -------------------- ---------- --  --------
     1  Eve         [email protected]        1500   HR
     2  Jon         [email protected]        2500   AD
     3  Mike       [email protected]        3000   AD
     4  Paul       [email protected]        3200   HR
     5  Mary       [email protected]        1800   IT
     6  Jane        [email protected]       2200   IT
Very good See the column AGE is no longer in our table .

3- ALTER TABLE Modify Column

-this is used when we want to change the data type of a column Sintax: 
ALTER TABLE table_name MODIFY column (column name) "new data type";
-so for this example will try alter the column DE from char(20) to char(10). Example: -firts we will see the table description do confirm that the column is char(20). 
mysqldesc test;
+--------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | YES |   | NULL  |    |
| name  | varchar(20) | YES |   | NULL  |    |
| email | varchar(20) | YES |   | NULL  |    |
| salary | int(11)   | YES |   | NULL  |    |
| <a style="color: red;">de | char(20)  | YES |   | NULL  |    |</a><a>
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
</a>
  • we can see that the column "de" is a char od 20 .
Now let's change the type or the size of the column, make it char of 30 : 
mysql
alter table test modify column de char(30);Query OK, 6 rows affected (0.27 sec)Records: 6 Duplicates: 0 Warnings: 0
Ok, now check the table structure and see the change that we have made 
mysqldesc test;
+--------+-------------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id   | int(11)   | YES |   | NULL  |    |
| name  | varchar(20) | YES |   | NULL  |    |
| email | varchar(20) | YES |   | NULL  |    |
| salary | int(11)   | YES |   | NULL  |    |
|<a style="color: red;">de  | char(30)  | YES |   | NULL  |    |</a>
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

4-ALTER TABLE Rename Column

  • this command will let use change the name of a column
Sintax: 
alter table "table name" change "old column name"
           "new column name" "data type for the new column";
Note: if you already have data in the table make sure the data types will apply to the data the column has in. -also the constraints must be applied to the column. Example: 
mysqlalter table test change name full_name varchar(20);
Query OK, 6 rows affected (0.16 sec)
Records: 6 Duplicates: 0 Warnings: 0
Now check the table structure: 
mysqldesc test;
+-----------+-------------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id    | int(11)   | YES |   | NULL  |    |
|<b style="color: red;">full_name|varchar(20) | YES |   | NULL  |    |</b>
| email   | varchar(20) | YES |   | NULL  |    |
| salary  | int(11)   | YES |   | NULL  |    |
| de    | char(30)  | YES |   | NULL  |    |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

5-ALTER TABLE Add/Drop Index

-with the Add index statement one can add an index to the table. Syntax: 
alter table "table name" add
index "index name"(column that will represent the index);
Example: -we will add an index to our table test. -check the table if we any indexes created . 
mysql
desc test;+-----------+-------------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
|<b style="color: red;">id   | int(11)   | YES |   | NULL  |    |</b>
| full_name | varchar(20) | YES |   | NULL  |    |
| email   | varchar(20) | YES |   | NULL  |    |
| salary  | int(11)   | YES |   | NULL  |    |
| de    | char(30)  | YES |   | NULL  |    |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
-we see that the column "id" has no index on it, so we will create one 
mysql
alter table test add index id_pk(id);
Query OK, 6 rows affected (0.18 sec)
Records: 6 Duplicates: 0 Warnings: 0
-check our new index: 
mysqldesc test;
+-----------+-------------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
|<b style="color: red;">id   | int(11)   | YES | <b style="color: #000000;">MUL</b>| NULL  |    |</b>| full_name | varchar(20) | YES |   | NULL  |    |
| email   | varchar(20) | YES |   | NULL  |    |
| salary  | int(11)   | YES |   | NULL  |    |
| de    | char(30)  | YES |   | NULL  |    |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
To get more info about your index use this command Sintax: 
Show index from "table name";
-this command will bring data about all the indexes found on the table. 
mysql
SHOW INDEX FROM test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test |     1 | id_pk  |      1 | id     | A     |         2 |   NULL | NULL  | YES | BTREE   | NULL  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Syntax to drop an index:

alter table "table name" drop index "index name";

Copyright 2020. All Rights Reserved