SQL ALTER TABLE

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

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

Note:

1. 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

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