ALTER Table

In SQL, the ALTER TABLE statement is used to modify the structure of an existing table in a database. It allows you to add, modify, or delete columns, as well as add or remove constraints on the table. The ALTER TABLE statement is useful when you need to make changes to the existing schema without dropping and recreating the entire table.

Here are some common operations you can perform with the ALTER TABLE statement:

Adding a New Column:
To add a new column to an existing table, you can use the ADD keyword followed by the column definition with its data type.

ALTER TABLE table_name
ADD column_name data_type [constraints];

Example:

ALTER TABLE Students
ADD Email VARCHAR(100);

Modifying a Column:
To modify the data type or other attributes of an existing column, you can use the ALTER COLUMN keyword.

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type [new_constraints];

Example:

In this example, we are modifying the "Age" column to make it a mandatory field (NOT NULL).

Dropping a Column:
To remove a column from an existing table, you can use the DROP COLUMN keyword.

Example:

In this example, we are dropping the "GPA" column from the "Students" table.

Adding Constraints:
You can also use ALTER TABLE to add constraints to existing columns. For example, adding a PRIMARY KEY or FOREIGN KEY constraint.

Example:

In this example, we are adding a primary key constraint on the "StudentID" column, making it the primary key of the "Students" table.

It's important to note that the specific syntax and available options for the ALTER TABLE statement may vary depending on the database management system you are using (e.g., MySQL, PostgreSQL, SQL Server, etc.). Additionally, be cautious when making changes to the table's structure, especially in production environments, and ensure you have appropriate backups and permissions before executing ALTER TABLE statements.