SQL: ALTER command

SQL ALTER command is used for altering the table structure. It can be used to:

• Add one or more columns to an existing table
• Rename any existing column
• Rename a table name
• Change the data type of one or more columns or modify its size
• Drop a column from the table

Let's look at various operations of ALTER command on a table or a column.

Example: Add a New Column to the Table

Using ALTER command, we can add a column to an existing table. Use ADD keyword with ALTER TABLE statement to add a new column to a table.

Syntax:

ALTER TABLE table_name 
ADD column_name datatype;

The below SQL query will add a new column address to the table Student, which will hold data of type VARCHAR which is nothing but a string of length 200.

ALTER TABLE Student 
ADD address VARCHAR(200);

Example: Add Multiple New Columns to the Table

Using ALTER command, we can even add more than one new column to an existing table. Use ADD keyword with ALTER TABLE statement to add multiple columns separated by a comma to a table.

Syntax

ALTER TABLE table_name 
ADD (column_name1 datatype1, column_name2 datatype2, column_name3 datatype);

The below SQL query will add three new columns father_name, mother_name, and dob with their respective data types to the Student table.

ALTER TABLE Student 
ADD (father_name VARCHAR(60), mother_name VARCHAR(60), dob DATE); 

Example: Add Column with Default Value

Using ALTER command, we can add a new column to an existing table with a default value. Use DEFAULT keyword with ALTER TABLE statement to add a default value to a new column to a table. The default value is used when no value is inserted in the column.

Syntax:

ALTER TABLE table_name 
ADD column_name datatype DEFAULT value;

The below SQL query will add a new column with a preset default value of '01-Jan-99' to the table Student.

ALTER TABLE Student 
ADD dob DATE DEFAULT '01-Jan-99';

Example: Modify an Existing Column

Using ALTER command, we can also modify or change the data type of an existing column. Use MODIFY keyword with ALTER TABLE statement to modify a column in a table.

Syntax:

ALTER TABLE table_name
MODIFY column_name new_datatype;

Remember we added a new column address in the beginning. The below SQL query will modify the address column of the Student table to now hold up to 300 characters. You can even use this statement if you have created columns with wrong data types to avoid errors.

ALTER TABLE Student 
MODIFY address varchar(300); 

Example: Rename a Column

Using ALTER command, we can rename an existing column. We use RENAME keyword with ALTER TABLE statement to provide a new name to an existing column. TO is also used to change the old name to a new name of the column.

Syntax

ALTER TABLE table_name 
RENAME old_column_name TO new_column_name;

The below SQL query will rename the address column to the location column.

ALTER TABLE Student 
RENAME address TO location; 

Example: Rename a Table

Using ALTER command, we can also rename an existing table. We use RENAME TO statement with ALTER TABLE statement to provide a new name to an existing table.

Syntax

ALTER TABLE table_name 
RENAME TO new_table_name;

The below SQL query will rename the Student table to the Student_Details table.

ALTER TABLE Student 
RENAME TO Student_Details; 

Example: Drop a Column

Using ALTER command, we can also drop or remove a column from the table. Note that the DROP keyword will permanently remove the column. So, be careful while using it.

ALTER TABLE table_name 
DROP column_name;

The below SQL query will drop the address column from the table Student.

ALTER TABLE Student 
DROP address; 

Conclusion

In this article, we discussed different operations on tables and columns using ALTER TABLE command with the help of examples. We used different keywords like ADD, MODIFY, RENAME, DROP to add, modify, rename and drop a column respectively.

---

---