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.

Demo Database

Let us view the database that we will use here, using the SELECT query.
Syntax: 

SELECT * FROM tableName ;

Query: 

Select * from dataflair_employee ;

We have 5 columns, namely:

1. name_emp
2. post_emp
3. email
4. age
5. salary

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:

Syntax:

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

ALTER TABLE tableName
ADD columnName datatype ;

Example 1: Let us add a gender column in the table.
Query:

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.

Syntax:

ALTER TABLE tableName
MODIFY columnName columnType;

Example 1: Let us modify the size of our name column to 100 in varchar.
Query:

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

Syntax:

ALTER TABLE tableName
RENAME TO newTableName 

Example 1: Let us rename our table from DataFlair_Employee to DataFlair_Info.

Query:

ALTER TABLE dataflair_employee
RENAME TO DataFlair_Info ;

Renaming The Column in SQL

Syntax:

ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;

Example 1: Let us rename our name_emp column to employeeName.
Query:

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

ALTER TABLE tableName
DROP COLUMN columnName ;

Example 1: Let us DROP the gender column from our DataFlair_info database.
Query:

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.

Syntax: 

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

TRUNCATE TABLE  dataflair_info;

Example 2: Let us drop our table.
Query: 

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.

Summary

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.