Deleting a Column From a Table in SQL

1. Overview

Relational databases can be managed and modified with the help of Structured Query Language (SQL). For instance, we can use SQL to alter tables by deleting columns from them. Deleting a column may help remove a redundant column or optimize the database by reducing its size.

In this tutorial, we explore how to delete a column from a table in SQL.

2. Deleting a Column From a SQL Table

SQL tables consist of columns and rows, where each column represents a unique attribute, and each row represents a record. The ALTER TABLE statement, on the other hand, facilitates adding, deleting, or modifying columns in an existing table. In this example, we’ll utilize this statement to delete a column:

ALTER TABLE Table_name DROP COLUMN column_name;

The syntax is straightforward:

• ALTER TABLE Table_name – instructs the database that we want to make changes to the table with the name Table_name
• DROP COLUMN column_name – specifies we want to remove the column with the name column_name

Relational database management systems like MySQL, PostgreSQL, and SQL Server support this syntax. However, there might be slight variations depending on the system.

To demonstrate, we’ll use the Student table from the Baeldung University database schema. Columns such as id, name, national_id, birth_date, enrollment_date, graduation_date, and gpa are included in this table.

Suppose we no longer require the national_id column:

ALTER TABLE Student DROP COLUMN national_id;

This command works for MySQL, PostgreSQL, and SQL Server to permanently delete the national_id column from the Student table.

Now, let’s use this syntax to delete multiple columns. In MySQL, we specify each column with its DROP COLUMN statement:

ALTER TABLE Student DROP COLUMN national_id, DROP COLUMN birth_date;

Meanwhile, in PostgreSQL and SQL Server, we list the columns after the DROP COLUMN statement, separated by commas:

ALTER TABLE Student DROP COLUMN national_id, birth_date;

To demonstrate deleting multiple columns, we delete the national_id and birth_date columns from the Student table.

3. Considerations and Best Practices

There are some factors we should consider when deleting a column in SQL.

One factor is the loss of data. We need to proceed cautiously when deleting a column since the deletion leads to losing all data in that particular column. Therefore, we need to ensure that we either have a backup of the column data or that the data is no longer necessary.

In case we need the column after deletion, we can restore it using the ALTER TABLE statement to re-add the column and restore the data from a backup if available. For instance, if we delete the national_id column and then decide that it’s important, we can re-add it:

ALTER TABLE Student ADD COLUMN national_id VARCHAR(15);

This command only restores the column, but not the data that was in the column.

Another factor is checking for any dependencies on the column we want to delete. An example is checking for foreign key relationships, stored procedures, and views. Deleting a column that’s referenced elsewhere may cause errors or break the database functionality.

Additionally, we need to make changes to parts of the application that rely on the column about to be deleted.

We can also test the deletion of a column in a development environment before making any modifications to a production database. This helps to discover any difficulties that might result from this deletion.

Further, if there is documentation about the database, we can update this documentation to indicate the modifications from deleting the column.

4. Conclusion

In this article, we delved into deleting a column from a table in SQL.

Deleting a column from a table in SQL helps to maintain the efficiency and relevance of a database. However, we need to be careful since the effects of this operation are not reversible.

We can effectively and safely delete a column by following best practices such as backing up data, checking for dependencies, and testing these changes in a development environment.