SQL Alter Table

Overview

The ALTER TABLE statement is used to modify the structure of an existing table in a database. This can include adding, dropping, or modifying columns, as well as changing table constraints.

Syntax

The basic syntax for altering a table is as follows:

ALTER TABLE table_name
    [ADD column_name datatype]
    [DROP COLUMN column_name]
    [MODIFY COLUMN column_name datatype];

Examples

1. Adding a New Column

To add a new column named age of type INT to the users table:

ALTER TABLE users
ADD age INT;

2. Dropping a Column

To drop an existing column named age from the users table:

ALTER TABLE users
DROP COLUMN age;

3. Modifying a Column

To change the data type of the username column to VARCHAR(100):

ALTER TABLE users
MODIFY COLUMN username VARCHAR(100);

Considerations

• Be careful when dropping columns, as this will permanently delete data.
• Some database systems may have specific syntax or limitations, so always check the documentation for your SQL dialect.
• When modifying columns, ensure that the new data type is compatible with existing data.

Conclusion

The ALTER TABLE statement is a powerful tool for managing and modifying database structures. Understanding how to add, drop, and modify columns is essential for effective database management.