Previous
Written by: rJTutorial Team | Last Updated:

SQL ALTER TABLE

In a perfect world, database schemas would be designed once and never change. In reality, software requirements evolve. You might need to add a new feature that requires storing more data, or you might need to change a data type to accommodate larger numbers. The ALTER TABLE statement is the primary tool used to modify an existing table structure without losing the data already stored within it.

Developer Tip: Before running an ALTER TABLE command on a production database, always perform a backup. Large tables can also "lock" during these operations, meaning other users might not be able to read or write to the table until the change is complete.

 

Key Features of SQL ALTER TABLE

The ALTER TABLE command is versatile and acts as a wrapper for several different structural modifications:

  • Add: Insert one or more new columns into an existing table.
  • Modify: Change the attributes of an existing column, such as increasing a VARCHAR length or changing a column from NULL to NOT NULL.
  • Drop: Permanently remove a column or a constraint (like a Foreign Key or Unique index).
  • Rename: Change the name of a column or the entire table to better reflect its purpose as the project grows.

 

SQL ALTER TABLE Syntax

The syntax for ALTER TABLE depends on the specific action you are taking. While most SQL dialects (MySQL, PostgreSQL, SQL Server) follow these patterns, always check your specific database documentation for minor variations.

// Adding a new column
ALTER TABLE table_name 
ADD column_name datatype constraint;
// Changing an existing column's definition
ALTER TABLE table_name 
MODIFY column_name new_datatype;

// Removing a column entirely
ALTER TABLE table_name 
DROP COLUMN column_name;
Best Practice: When adding new columns to a table that already contains data, consider providing a DEFAULT value. This ensures that existing rows have a sensible value instead of just NULL.

 

Example: Adding a Column

Imagine you have an Employees table, and your HR department now requires their email addresses for a new company newsletter. You can add this field easily:

ALTER TABLE Employees 
ADD Email VARCHAR(100);
  • This command creates a new column named Email.
  • It can store up to 100 characters.
  • By default, existing rows will have a NULL value in this new column.

 

Example: Modifying a Column

If your company expands and salaries increase, the original numeric limit you set for the Salary column might become too small. You can modify the data type to allow for larger numbers.

ALTER TABLE Employees 
MODIFY Salary DECIMAL(12,2);
  • This updates the Salary column to a DECIMAL type.
  • It allows for 12 total digits, with 2 of those reserved for decimals (e.g., 999,999,999.99).
Watch Out: Be careful when reducing the size of a column (e.g., changing VARCHAR(255) to VARCHAR(50)). If any existing data is longer than the new limit, the database may throw an error or truncate your data, leading to data loss.

 

Example: Dropping a Column

Sometimes data becomes redundant. If you decide you no longer need to track an employee's age because you already have their birthdate, you can remove the column.

ALTER TABLE Employees 
DROP COLUMN Age;
Common Mistake: Dropping a column is irreversible in most SQL environments. Once you execute this command, the data in that column is gone forever. Double-check if that column is being used in any application code, views, or stored procedures before dropping it.

 

Example: Renaming a Column

Naming conventions change. If a column named Name is confusing because it doesn't specify if it's a first or last name, you might rename it to FullName.

ALTER TABLE Employees 
RENAME COLUMN Name TO FullName;
  • This keeps all the data and constraints intact but changes the identifier used to access it.

 

Example: Renaming a Table

You might need to rename a table to better align with a new naming convention or project restructuring.

ALTER TABLE Employees 
RENAME TO Staff;
  • The table formerly known as Employees is now globally recognized as Staff.

 

Summary

  • The ALTER TABLE statement is the standard way to evolve your database schema over time.
  • It allows you to add, modify, drop, or rename structural elements.
  • Always verify your changes on a development or staging environment first, as schema changes can impact application performance and data integrity.
Previous