Alter Command In SQL | Truncate Table In SQL
SQL provides us with the flexibility to modify and delete a table after it’s creation. We have data that needs to be put in the table and, at the same time, data that is of no use after a particular time.
To keep our data updated, we need to take various measures, and table altering is one of those. Alter and Drop commands are applicable for the same.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
Let us view the database that we will use here, using the SELECT query.
SELECT * FROM tableName ;
Select * from dataflair_employee ;
We have 5 columns, namely:
What is an Alter Command in SQL?
Alter command is applicable to add, modify, or update columns in an existing table. It is also applicable to drop or delete a table. The basic syntax of the alter command is as follows:
ALTER TABLE tableName operation columnName datatype;
We can perform the following tasks using the Alter Command:
1. ADD Column in SQL
Used to add a new column to the existing table.
ALTER TABLE tableName ADD columnName datatype ;
Example 1: Let us add a gender column in the table.
ALTER TABLE dataflair_employee ADD gender varchar(10);
2. MODIFY Column in SQL
Used to modify the already existing columns in a database. We can modify one or multiple columns at once.
ALTER TABLE tableName MODIFY columnName columnType;
Example 1: Let us modify the size of our name column to 100 in varchar.
ALTER TABLE dataflair_employee MODIFY name_emp varchar(100);
We can see the name_emp column has changed in size from varchar(50) to varchar(100).
3. RENAME Column in SQL
Rename is put with Alter to change the name of a column. This could be applicable due to various reasons, for example:
- To make the column name meaningful.
- Simplification of names for analysis.
Let us rename our table and also the columns of our table.
Renaming the Table in SQL
ALTER TABLE tableName RENAME TO newTableName
Example 1: Let us rename our table from DataFlair_Employee to DataFlair_Info.
ALTER TABLE dataflair_employee RENAME TO DataFlair_Info ;
Renaming The Column in SQL
ALTER TABLE table_name CHANGE COLUMN old_name TO new_name;
Example 1: Let us rename our name_emp column to employeeName.
ALTER TABLE dataflair_info CHANGE COLUMN `name_emp` `employeeName` VARCHAR(100) ;
4. DROP Column in SQL
We use drop to remove the columns which are no longer required in the database. We should be very careful while we drop a column as data we delete once can’t be retrieved.
ALTER TABLE tableName DROP COLUMN columnName ;
Example 1: Let us DROP the gender column from our DataFlair_info database.
ALTER TABLE dataflair_info DROP COLUMN gender ;
We can see the gender column is no longer available in our database.
What is the Truncate Command in SQL?
We use the Truncate command to delete the data stored in the table. The working of truncate command is similar to the working of Delete command without a where clause.
TRUNCATE TABLE tableName ;
Difference between Truncate and Drop command in SQL?
Executing the truncate command deletes all the data stored in the table or database. Whereas the Drop command is applicable to destroy the whole object it could be a table, a database, or a schema.
Also, syntax differences exist. Syntax of both are as follows:
SQL Truncate Syntax:
TRUNCATE TABLE tableName ;
Syntax of Drop:
DROP TABLE tableName ;
Example 1: Let us drop the table DataFlair_Info.
TRUNCATE TABLE dataflair_info;
Example 2: Let us drop our table.
DROP TABLE dataflair_info ;
Some of the Restrictions on Alter table are as follows:
- By using an alter table, we can’t change the name of the table.
- We can’t modify the names of the columns.
- We can’t alter a column already containing data.
Thus, we have seen the alter and the truncate command in SQL. We use the alter command for multiple functionalities i.e., to add columns, modify columns, and many more.
With these commands, we can easily modify our database. We need to be very attentive while truncating the data as once a database point strikeout, we can’t recover it back.