SQL ALTER TABLE

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will learn how to use the ALTER TABLE statement in SQL. The ALTER TABLE statement is used to modify the structure of an existing table. This includes adding, deleting, or modifying columns, and adding or dropping constraints. This chapter will cover the syntax, usage, and examples to help you understand how to use the ALTER TABLE statement effectively.

Syntax for ALTER TABLE

The ALTER TABLE statement has different syntaxes depending on the modification you want to make. Here are some common operations:

1. Add a Column

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE employees
ADD date_of_birth DATE;

2. Drop a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Example

ALTER TABLE employees
DROP COLUMN date_of_birth;

3. Modify a Column

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Example

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(150);

4. Rename a Column

MySQL

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

Example

ALTER TABLE employees
CHANGE COLUMN email email_address VARCHAR(150);

SQL Server

EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Example

EXEC sp_rename 'employees.email', 'email_address', 'COLUMN';

5. Add a Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Example

ALTER TABLE employees
ADD CONSTRAINT uc_email UNIQUE (email);

6. Drop a Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE employees
DROP CONSTRAINT uc_email;

Step-by-Step Example

1. Create a Sample Table

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

2. Add a Column

ALTER TABLE employees
ADD date_of_birth DATE;

3. Verify the Column Addition

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email	varchar(100)	YES	UNI	NULL
date_of_birth	date	YES		NULL

4. Modify a Column

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(150);

5. Verify the Column Modification

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email	varchar(150)	YES	UNI	NULL
date_of_birth	date	YES		NULL

6. Rename a Column

MySQL

ALTER TABLE employees
CHANGE COLUMN email email_address VARCHAR(150);

SQL Server

EXEC sp_rename 'employees.email', 'email_address', 'COLUMN';

7. Verify the Column Rename

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email_address	varchar(150)	YES	UNI	NULL
date_of_birth	date	YES		NULL

8. Add a Constraint

ALTER TABLE employees
ADD CONSTRAINT uc_email UNIQUE (email_address);

9. Drop a Constraint

ALTER TABLE employees
DROP CONSTRAINT uc_email;

Conclusion

The ALTER TABLE statement is used for modifying the structure of existing tables in a database. It allows you to add, delete, and modify columns, as well as add and drop constraints. Understanding how to use the ALTER TABLE statement effectively is essential for managing and evolving your database schema.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

🆕 High-Demand 80–90% OFF

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

🆕 High-Demand 80–90% OFF

ChatGPT + Generative AI + Prompt Engineering for Beginners

🚀 Trending Now 80–90% OFF

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Real-Time REST APIs with Spring Boot - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Microservices with Spring Boot and Spring Cloud

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Java Full-Stack Developer Course with Spring Boot and React JS

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

🌟 Top Rated 80–90% OFF

Testing Spring Boot Application with JUnit and Mockito

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot Thymeleaf Real-Time Web Application - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Master Spring Data JPA with Hibernate

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot + Apache Kafka Course - The Practical Guide

🎓 Student Favorite 80–90% OFF
Available in Udemy for Business

Java Testing: Mastering JUnit 5 Framework

🆕 New 80–90% OFF

Reactive Programming in Java: Spring WebFlux and Testing

🌟 Top Rated 80–90% OFF

Spring Boot + RabbitMQ Course - The Practical Guide

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Functional Programming in Java (Includes Java Collections)

🎓 Student Favorite 80–90% OFF

ChatGPT for Java Developers: Boost Your Productivity with AI

🚀 Trending Now 80–90% OFF