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.
Table of Contents
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
Q1. Does the ALTER command support changing an old column’s data type?
A. Yes you can use ALTER for this use case. Here’s an example:ALTER TABLE employees
MODIFY COLUMN employee_id VARCHAR(10);
Q2. How to set a default value to a column using ALTER?
A. You can add a default value to an existing column using the ALTER command by using SET DEFAULT. Here’s an example:ALTER TABLE employees
ALTER COLUMN employee_id SET DEFAULT 'UNKNOWN';
Q3. Can SQL I remove a column from a table?
A. The ALTER TABLE and DROP COLUMN commands can be used to drop a column. Here’s an example of dropping department_id from the employees table:ALTER TABLE employees
DROP COLUMN department_id;
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.
