Modifying Table Structure Using ALTER TABLE Statements

In SQL, the ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add, modify, or drop columns, as well as make changes to constraints and indexes. Here’s an overview of how to use ALTER TABLE statements to modify table structure.

Table of Contents

Adding Columns

To add a new column to an existing table, you can use the ALTER TABLE statement with the ADD COLUMN clause.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

table_name: Specifies the name of the table where the column will be added.
new_column_name: Specifies the name of the new column.
data_type: Specifies the data type of the new column.

To add a new column called “email” of data type VARCHAR(255) to the “employees” table, you can use following code.

ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

Modifying Columns

To modify the properties or data type of an existing column, you can use the ALTER TABLE statement with the ALTER COLUMN clause.

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;

table_name: Specifies the name of the table containing the column to be modified.
column_name: Specifies the name of the column to be modified.
new_data_type: Specifies the new data type for the column.

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(10,2);

This query modifies the “salary” column in the “employees” table, changing its data type to DECIMAL(10,2).

Dropping Columns

To remove a column from an existing table, you can use the ALTER TABLE statement with the DROP COLUMN clause.

ALTER TABLE table_name
DROP COLUMN column_name;

table_name: Specifies the name of the table from which the column will be dropped.
column_name: Specifies the name of the column to be dropped.

ALTER TABLE employees
DROP COLUMN email;

This query removes the “email” column from the “employees” table.

Adding Constraints

You can use the ALTER TABLE statement to add constraints to an existing table. Here’s an example of adding a primary key constraint.

ALTER TABLE table_name
ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column_name);

table_name: Specifies the name of the table where the constraint will be added.
pk_constraint_name: Specifies the name of the primary key constraint.
column_name: Specifies the column(s) to be included in the primary key.

ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

This query adds a primary key constraint named “pk_employee_id” to the “employees” table, using the “employee_id” column as the primary key.

There are other types of constraints you can add, such as UNIQUE, FOREIGN KEY, and CHECK constraints. The syntax for each type of constraint may vary.

With ALTER TABLE statement, you can modify the structure of an existing table in your database. Whether you’re adding columns, modifying column properties, dropping columns, or adding constraints, the ALTER TABLE statement provides flexibility in adjusting the table structure to meet your evolving needs.