The SQL ALTER TABLE command, a part of Data Definition Language (DDL), modifies the structure of a table. It can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. This command also changes table characteristics like the storage engine. Structured Query Language’s ALTER TABLE statement allows adding, modifying, and deleting columns of an existing table, as well as adding or removing various SQL constraints.
Syntax
ALTER TABLE table_name
action;Where:
table_name: Specifies the name of the table to be altered.action: Specifies the modification to be made, such as adding, modifying, or dropping columns, constraints, or indexes.
Example
firstly we will create a student table of a class that contains the record of a student
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 |
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_name
ADD column_name datatype;ALTER TABLE Student
ADD 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 |
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 Student
ADD 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 |
Conclusion
The ALTER TABLE statement in SQL is a powerful tool for modifying the structure of existing database tables. Whether you need to add, modify, or drop columns, constraints, or indexes, ALTER TABLE provides the flexibility to make these changes without the need to recreate the entire table. This feature is crucial for database administrators and developers as it allows them to adapt the database schema to evolving requirements without disrupting existing data or application functionality.
However, it’s essential to use ALTER TABLE with caution, especially in production environments, as any structural changes can potentially impact data integrity and application performance. Proper testing and validation of alterations are recommended to ensure that the changes behave as expected and do not introduce unintended consequences.
Overall, the ALTER TABLE statement is a fundamental component of SQL database management, providing the ability to adapt database structures over time to meet the evolving needs of applications and users.
Frequently Asked Questions
The ALTER TABLE statement in SQL is used to modify the structure of an existing table. This includes adding, modifying, or dropping columns, constraints, or indexes.
Yes, you can use the ALTER TABLE statement to add a new column to an existing table. The syntax for adding a column is ALTER TABLE table_name ADD column_name datatype [constraint];.
To modify the datatype of an existing column, you can use the ALTER TABLE statement with the syntax ALTER TABLE table_name ALTER COLUMN column_name datatype;.
Yes, you can drop a column from a table using the ALTER TABLE statement. The syntax for dropping a column is ALTER TABLE table_name DROP COLUMN column_name;.
Yes, you can add or remove constraints using ALTER TABLE. To add a constraint, use the syntax, and to remove a constraint, use ALTER TABLE table_name DROP CONSTRAINT constraint_name;
