Description
SQL Alter Table statement is used to add, modify, or delete a columns from the existing table.
SQL Alter Table command performs adding of column or modifying column size and is divided into two types.
- Add
- Modify
Conceptual
figure
Description
Using add command new columns can be included in the current SQL Alter Table.
Syntax
Alter table<table_name>
ADD(<column_name1> datatype(size),<column_name2> datatype(size));
Table_name => Any accurate table name.
Column_name =>The operation that can be performed on a column in the table.
Examples
By viewing the below example, the table can be altered by utilizing alter adding command.
[c]sql> alter table student add(fee number(7,2));
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
sql>desc student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id | int(11) | YES | | NULL | |
| stu_name | varchar(255) | YES | | NULL | |
| stu_result | varchar(255) | YES | | NULL | |
| branch | varchar(255) | YES | | NULL | |
| fee | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
sql> alter table student add(course varchar(255));
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
sql>desc student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id | int(11) | YES | | NULL | |
| stu_name | varchar(255) | YES | | NULL | |
| stu_result | varchar(255) | YES | | NULL | |
| branch | varchar(255) | YES | | NULL | |
| fee | int(11) | YES | | NULL | |
| course | varchar(255) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)[/c]
The above example tells that, how to add a new column_name like fee and course with size in a student table.
Description
The structure of an existing table object can be modified using modify command.
Syntax
Alter table
modify( datatype(size));
Table_name => Any accurate table.
Column_name =>The operation that can be performed on a column in the table.
Examples
By viewing the below example, the concept of modify command can be easily understood.
[c]sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id | int(11) | YES | | NULL | |
| stu_name | varchar(255) | YES | | NULL | |
| stu_result | varchar(255) | YES | | NULL | |
| branch | varchar(250) | YES | | NULL | |
| fee | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
sql> alter table student modify branch varchar2(20);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id | int(11) | YES | | NULL | |
| stu_name | varchar(255) | YES | | NULL | |
| stu_result | varchar(255) | YES | | NULL | |
| branch | varchar2(20) | YES | | NULL | |
| fee | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
sql> alter table student modify stu_name varchar2(20);
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
sql> desc student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| stu_id | int(11) | YES | | NULL | |
| stu_name | varchar2(20) | YES | | NULL | |
| stu_result | varchar(255) | YES | | NULL | |
| branch | varchar2(20) | YES | | NULL | |
| fee | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)[/c]
The above example tells that, how to Modify a column_name branch and stu_name with size in student table.
Key Points
- SQL Alter Table - Alter table will add or modify the structure of the table.