SQL ALTER TABLE Command

By | October 2, 2021
SQL ALTER TABLE Command

The SQL ALTER command is limited to the table columns, if we want to modify, update, create or delete a table column or the column constraints then we can use the ALTER TABLE command. ALTER command always use with different clauses such as ADD, MODIFY, DROP etc, which specify which operation should be performed on the table.

SQL ALTER TABLE Command

<Note>: For all the examples we have mentioned below are based on this Students Table.

Vamware
+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

ADD a new Column in the Table

To add a new column in an existing table we can use the ALTER TABLE command with ADD clause. This command comes useful when we want to add a new field in the table.

ADD Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example

Query: ADD a new column ContactNo in table Students.

ALTER TABLE Students
ADD ContactNo INT(10);

Verify the table

Select * from Students;
+------+--------+------+--------+-------+----------+-----------+
| id   | name   | age  | grades | marks | Trade    | ContactNo |
+------+--------+------+--------+-------+----------+-----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |      NULL |
|    2 | Naruto |   18 | A      |   960 | Humanity |      NULL |
|    3 | Zoro   |   20 | A      |   940 | Commerce |      NULL |
|    4 | Sanji  |   21 | B      |   899 | Humanity |      NULL |
|    5 | Nami   |   17 | B      |   896 | Science  |      NULL |
|    6 | Robin  | NULL | B      |   860 | Humanity |      NULL |
|    7 | Goku   |   27 | B      |   860 | Humanity |      NULL |
+------+--------+------+--------+-------+----------+-----------+

Using the ALTER TABLE- ADD command we can add constraints to a table:

ALTER TABLE table_name 
ADD CONSTRAINT key PRIMARY KEY (column_name_1, column_name_2...);

Delete Table Columns(ALTER TABLE-DROP )

To delete a specific column from a table we can use the ALTER TABLE command with DROP clause.

ALTER TABLE-DROP Syntax

ALTER TABLEtable_name
DROP COLUMN column_name;

Example

Query: Delete the ContactNo Column from the table Students:

ALTER TABLE Students
DROP COLUMN ContactNo;

ALTER-DROP command is also capable of removing a table column constrain and INDEX.

ALTER TABLE table_name 
DROP CONSTRAINT Constraint_Name;
ALTER TABLE table_name 
DROP INDEX Index_Name;

ALTER TABLE-MODIFY

To Modify the existing column in the table we can use the ALTER TABLE command with MODIFY clause. MODIFY comes useful when we want to change the column data type.

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Example

Query: Change the Size of the name column:

ALTER TABLE Students
MODIFY COLUMN name VARCHAR(100);

Query OK, 7 rows affected (1.94 sec)
Records: 7  Duplicates: 0  Warnings: 0

Summary

  • ALTER TABLE command is used to modify or make changes in the Table columns.
  • Using the ADD, DROP and MODIFY clauses we can add a new column, delete an existing column or change the size and data type of the existing column.
  • ADD and DROP commands can also be used to add and delete the Constraints and index from the table columns.

People are also reading:

Leave a Reply

Your email address will not be published. Required fields are marked *