Altering a Table

Definition: The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It allows you to change the structure of your database without having to delete and recreate the entire table.

Why: Requirements for software projects often change over time. Adding a new column to an existing table is considered a core database operation, as it allows your data structure to grow and adapt alongside your application.

Syntax: Adding a Column

To add a new category of data to your table, you use the ADD keyword followed by the new column name and its data type.

ALTER TABLE table_name
ADD column_name datatype;

Example: Adding Student Marks

If you decide you now need to track exam scores in your students table, you can add a marks column like this:

ALTER TABLE students
ADD marks INT;

Explanation

• Structural Change: This command changes the blueprint of the table. Every existing row in the table will now have a new "marks" field.
• Existing Data: When you add a new column to a table that already has data, the new column will initially be filled with NULL (empty) values for all existing rows.

Other Common ALTER Operations

Key Notes

• Data Loss Warning: Using DROP COLUMN will permanently delete all data stored in that column. Always back up your data before making structural changes.
• Constraints: You can also use ALTER TABLE to add constraints like UNIQUE or PRIMARY KEY to columns that were created previously.
• Performance: On extremely large tables (millions of rows), altering a table can take some time as the database needs to rewrite the underlying file structure.