• Articles
  • Tutorials
  • Interview Questions

ALTER TABLE Statement in SQL - ADD, DROP, MODIFY, RENAME

Tutorial Playlist

What is the SQL ALTER Command?

The ALTER command in SQL is a powerful tool that allows you to change the structure of existing database objects without affecting the data they contain. The command can be used to modify table columns, constraints, indexes, and more.

Modifying Table Structure

Adding Columns

You can use the ALTER TABLE statement to add new columns to an existing table. For example, let’s say you have a Customers table and want to add a Phone_Number column.

The syntax for adding a column is as follows:

ALTER TABLE Customers ADD Phone_Number varchar(20);

Dropping Columns

To remove a column from a table, you can use the DROP COLUMN clause in the ALTER TABLE statement. Suppose you decide to remove the “Phone_Number” column from the “Customers” table.

ALTER TABLE Customers
DROP COLUMN Phone_Number;

Modifying Column Data Types

The ALTER command also lets you change the data type of a column. Imagine you want to change the “Phone_Number” column data type to hold longer phone numbers.

ALTER TABLE Customers
ALTER COLUMN Phone_Number VARCHAR(20);

3. Managing Constraints

Adding Constraints

Constraints ensure data integrity. You can use the ALTER TABLE statement to add constraints like primary keys, foreign keys, and unique constraints. For example, let’s add a unique constraint to the “Email” column in the “Customers” table.

ALTER TABLE Customers
ADD CONSTRAINT UQ_Email UNIQUE (Email);

Dropping Constraints

To remove a constraint, use the DROP CONSTRAINT clause in the ALTER TABLE statement. If you wish to drop the unique constraint on the “Email” column:

ALTER TABLE Customers
DROP CONSTRAINT UQ_Email;

4. Renaming Tables

The ALTER command allows you to rename a table easily. Suppose you want to rename the “Customers” table to “Clients.”

ALTER TABLE Customers
RENAME TO Clients;

5. Changing Table Owners

You can change the owner of a table using the ALTER command, granted you have the necessary privileges.

ALTER TABLE Customers
OWNER TO NewOwner;

6. Real-world Examples

Adding Columns for Enhanced Data Tracking

Consider a scenario where an “Orders” table lacks a “Timestamp” column for order placement. You can use ALTER to rectify this.

ALTER TABLE Orders
ADD Timestamp DATETIME;

Adapting to Business Changes: Modifying Data Types

If an “Inventory” table’s “Product_Price” column needs more precision, you can modify its data type.

ALTER TABLE Inventory
ALTER COLUMN Product_Price DECIMAL(10, 2);

Strengthening Data Integrity: Adding Constraints

Suppose an “Employees” table requires a foreign key relationship with a “Departments” table. You can establish it using ALTER.

ALTER TABLE Employees
ADD CONSTRAINT FK_DepartmentID
FOREIGN KEY (Department_ID) REFERENCES Departments(ID);

Conclusion

Mastering the SQL ALTER command empowers you to adapt your database structures to changing requirements without compromising data integrity. Whether you’re adding columns, altering data types, or managing constraints, the ALTER command is a versatile tool in your database administration arsenal. Armed with the knowledge and practical examples provided in this guide, you’re now ready to navigate the world of SQL alterations with confidence.

Course Schedule

Name Date Details
SQL Training 30 Nov 2024(Sat-Sun) Weekend Batch View Details
07 Dec 2024(Sat-Sun) Weekend Batch
14 Dec 2024(Sat-Sun) Weekend Batch

About the Author

Data Engineer

As a skilled Data Engineer, Sahil excels in SQL, NoSQL databases, Business Intelligence, and database management. He has contributed immensely to projects at companies like Bajaj and Tata. With a strong expertise in data engineering, he has architected numerous solutions for data pipelines, analytics, and software integration, driving insights and innovation.