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.
+------+--------+------+--------+-------+----------+ | 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: