SQL ALTER TABLE Statement

Introduction

ALTER command is one of the most important SQL functions to know for database manipulation and management. This Data Definition Language (DDL) command can alter existing tables by adding, removing, or updating columns, constraints, and indexes. We’ll look at different ways to use the ALTER command to highlight its flexibility. In addition, ALTER TABLE statements in SQL may be used to change the data type of an existing column to match new data needs.

If you’re just starting out to explore SQL, here’s a beginner’s guide to help you: SQL For Data Science: A Beginner Guide

Overview

• Learn the purpose and functionality of the SQL ALTER command.
• Understand how and where to use SQL’s ALTER TABLE statements.
• Explore various scenarios and examples demonstrating the use of this SQL function.

Applications of ALTER Command

The ALTER command in SQL can be used for several purposes. Here are some common applications:

Sample Table

Here’s a sample table on which we will be testing out various ALTER TABLE statements.

Adding a New Column

To add a new column in our Employee table, you can use the ADD COLUMN statement.

ALTER TABLE employees
ADD COLUMN birth_date DATE;
SELECT emp_id, birth_date from Employees WHERE emp_id BETWEEN 1 AND 5;

This adds a new column named `birth_date` of type DATE in the table, but the values are empty as we just added a new column in Employees.

Modifying an Existing Column

To modify an existing column, use the MODIFY or ALTER COLUMN clause.

ALTER TABLE employees
MODIFY COLUMN birth_date DATETIME;

The `birth_date` column in the `employees` table changes from DATE to DATETIME type. Similarly we can change other data types as well for other columns.

Dropping a Column

To remove an existing column from your table, use the DROP COLUMN in SQL.

ALTER TABLE employees
DROP COLUMN birth_date;

This SQL query removes the birth_date column from the employees table.

Adding Constraints

The ALTER command’s uses extend to adding constraints to a table like adding a primary key, foreign key, or unique constraint.

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

This will add a primary key constraint named `pk_employee_id` on the `emp_id` column of the `employees` table.

Removing Constraints

We use the DROP CONSTRAINT to remove a constraint from the table.

ALTER TABLE employees
DROP CONSTRAINT pk_employee_id;

This will remove the primary key constraint (pk_employee_id) from the Employees table.

Renaming a Table

The ALTER command can also rename a table.

ALTER TABLE employees
RENAME TO staff;

This will rename the `employees` table to `staff`.

Conclusion

DDL’s ALTER command is very effective for managing and altering database table structures. Users can add, change, or delete columns and constraints, providing the flexibility required for adaptable database design and management. This command makes it easy to update database structures as needs change.

Learn More: SQL: A Full Fledged Guide from Basics to Advanced Level

Frequently Asked Questions

Mounish V

I'm a tech enthusiast, graduated from Vellore Institute of Technology. I'm working as a Data Science Trainee right now. I am very much interested in Deep Learning and Generative AI.