SQL ALTER TABLE : ADD, DROP, MODIFY, RENAME
ALTER TABLE in SQL is used to change the structure of the existing table. In this article, we will briefly discuss how to add, modify, drop, rename columns, constraints, and data type of a column in a table.
In the previous article, we discussed how to create a table, update the record, and avoid duplicates in the table. Now, what if you want to change the structure of the existing table?
In SQL, we can use ALTER TABLE command to change the table’s structure.
This article will briefly discuss how to use the ALTER command in SQL.
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is ALTER command in SQL?
ALTER TABLE is a DDL command in SQL that is used to change the structure of the existing table i.e. we can add/modify/drop/rename constraints and columns in the table or add another primary key to a table, and even can change the data type of a particular column.
Now, let’s have some examples to understand better how to use ALTER TABLE command in SQL.
So, firstly we will create a student table of a class that contains the record of a student who scored maximum marks in a particular subject using CREATE TABLE and INSERT command in SQL.
CREATE TABLE Student( RollNo int PRIMARY KEY, Name VARCHAR(100) NOT NULL, Gender TEXT NOT NULL; Subject VARCHAR(30), MARKS INT (3));
INSERT INTO Student VALUES (1, Vaibhav, M, Mathematic, 100);INSERT INTO Student VALUES (2, Vishal, M, Physics, 79);INSERT INTO Student VALUES (3, Saumya, F, Chemistry, 95);INSERT INTO Student VALUES (4, Arun, M, English, 78);INSERT INTO Student VALUES (5, Anjum, F, Hindi, 83);INSERT INTO Student VALUES (6, Radhika, F, Biology, 57);INSERT INTO Student VALUES (7, Harpreet, F, Physical Education, 68);
SELECT * FROM Student;
Output:
| RollNo | Name | Gender | Subject | Marks |
| 1 | Vaibhav | M | Mathematics | 100 |
| 2 | Vishal | M | Physics | 79 |
| 3 | Saumya | F | Chemistry | 95 |
| 4 | Arun | M | English | 78 |
| 5 | Anjum | F | Hindi | 83 |
| 6 | Radhika | F | Biology | 57 |
| 7 | Harpreet | F | Physical Education | 68 |
Must Read: SQL SELECT
Must Read: SQL DELETE
Now, we will see how to use the ALTER TABLE command in SQL to add/delete/modify/rename columns and constraints in a table.
SQL ALTER TABLE
ALTER TABLE ADD column
To add a new column to the existing table, firstly select the table with the ALTER TABLE command and then define the column name and the data type of that column.
Syntax
ALTER TABLE table_nameADD column_name datatype;
Example: Add a new column (State) in the Student table.
Query
ALTER TABLE StudentADD State TEXT;
SELECT * FROM Student;
Output
| RollNo | Name | Gender | Subject | Marks | State |
| 1 | Vaibhav | M | Mathematics | 100 | NULL |
| 2 | Vishal | M | Physics | 79 | NULL |
| 3 | Saumya | F | Chemistry | 95 | NULL |
| 4 | Arun | M | English | 78 | NULL |
| 5 | Anjum | F | Hindi | 83 | NULL |
| 6 | Radhika | F | Biology | 57 | NULL |
| 7 | Harpreet | F | Physical Education | 68 | NULL |
Must Read: SQL WHERE
Must Read: Introduction to SQL
Now, you have to use the UPDATE query in SQL to give the values to all rows of the State column.
Note:
- If you want to set any default value to the newly added column, you have to set the value while defining the column name and column data type. For example, if all the Student of a class belongs to the same state (Uttar Pradesh), then
Query
ALTER TABLE StudentADD State TEXT default “Uttar Pradesh”;
SELECT * FROM Student;
Output
| RollNo | Name | Gender | Subject | Marks | State |
| 1 | Vaibhav | M | Mathematics | 100 | Uttar Pradesh |
| 2 | Vishal | M | Physics | 79 | Uttar Pradesh |
| 3 | Saumya | F | Chemistry | 95 | Uttar Pradesh |
| 4 | Arun | M | English | 78 | Uttar Pradesh |
| 5 | Anjum | F | Hindi | 83 | Uttar Pradesh |
| 6 | Radhika | F | Biology | 57 | Uttar Pradesh |
| 7 | Harpreet | F | Physical Education | 68 | Uttar Pradesh |
Must Read: How to CREATE, UPDATE, INSERT and DELETE SQL views
Must Read: SQL Logical Operator
2. We can add multiple columns in a Table
Example: Add State and Phone Number in the Student Table
Query
ALTER TABLE StudentADD State TEXT, Phone INT (10);
| RollNo | Name | Gender | Subject | Marks | State | Phone |
| 1 | Vaibhav | M | Mathematics | 100 | NULL | NULL |
| 2 | Vishal | M | Physics | 79 | NULL | NULL |
| 3 | Saumya | F | Chemistry | 95 | NULL | NULL |
| 4 | Arun | M | English | 78 | NULL | NULL |
| 5 | Anjum | F | Hindi | 83 | NULL | NULL |
| 6 | Radhika | F | Biology | 57 | NULL | NULL |
| 7 | Harpreet | F | Physical Education | 68 | NULL | NULL |
Must Read: Introduction to Normalization
ALTER TABLE MODIFY column
The statement is used to modify or change the data type of an existing column.
Syntax
ALTER TABLE table_nameMODIFY COLUMN column_name data_type;
Example : Change the data type of the Marks column from Integer to VARCHAR.
Query
ALTER TABLE StudentMODIFY COLUMN Marks VARCHAR(3);
SELECT * FROM Student;
Must Read: What is the difference between SQL and MySQL?
ALTER TABLE RENAME column
This statement is used to rename the column name of a table. The syntax for this is similar to the ALTER TABLE MODIFY COLUMN.
Syntax
ALTER TABLE table_nameRENAME COLUMN column_name TO new_column_name;
Example: Change the column name of RollNo to EnrollementNo.
Query
ALTER TABLERENAME COLUMN RollNo TO EnrollementNo;
SELECT * FROM Student;
Output
| EnrollementNo | Name | Gender | Subject | Marks |
| 1 | Vaibhav | M | Mathematics | 100 |
| 2 | Vishal | M | Physics | 79 |
| 3 | Saumya | F | Chemistry | 95 |
| 4 | Arun | M | English | 78 |
| 5 | Anjum | F | Hindi | 83 |
| 6 | Radhika | F | Biology | 57 |
| 7 | Harpreet | F | Physical Education | 68 |
Must Read: Difference between SQL and NoSQL
ALTER TABLE DROP column
This statement removes the column in a table using the drop clause in SQL.
Syntax
ALTER TABLE table_nameDROP COLUMN column_name;
Example: From the student table, remove the Marks column.
Query
ALTER TABLE StudentDROP COLUMN Marks;
SELECT * FROM Student;
Output
| RollNo | Name | Gender | Subject |
| 1 | Vaibhav | M | Mathematics |
| 2 | Vishal | M | Physics |
| 3 | Saumya | F | Chemistry |
| 4 | Arun | M | English |
| 5 | Anjum | F | Hindi |
| 6 | Radhika | F | Biology |
| 7 | Harpreet | F | Physical Education |
Until now, we only use the ALTER TABLE command with the column. We can use ALTER TABLE command to Rename the table name.
Must Read: SQL LIMITS
Must Check: Database and SQL Online Course and Certifications
ALTER TABLE RENAME table
Syntax
ALTER TABLE table_nameRENAME TO new_table_name;
Conclusion
In this article, we have briefly discussed SQL ALTER TABLE command with examples.
Hope this article, will help you in your Data Science/Data Analysis joirney.
Keep Learning!!
Keep Sharing!!
Must Read: All about DML Command in SQL
Must Read: SQL ACID Properties
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
