Alter tables

The structure of a SQL database table - its columns, data types, constraints etc - is defined at the time of creation via CREATE TABLE. However, requirements evolve and changes need reflecting in the database schema.

SQL provides an ALTER TABLE command just for this purpose. It allows modifying existing tables through a number of actions like adding, dropping or renaming columns and constraints.

Add New Column

Adding a column allows capturing an additional data attribute when requirements change:

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(12);

Here we add a phone_number field to store customer phone numbers, which was not originally required. Adding new columns allows recording additional data as needs change.

Drop Column

Removing columns that are no longer necessary or storing obsolete data:

ALTER TABLE products
DROP COLUMN list_price;

The list_price column is no longer used, so we drop it to keep the table lean. Note - dropping results in permanent data loss from that column.

Rename Column

Renaming poorly named columns makes schemas more readable over time:

ALTER TABLE order_reviews
RENAME COLUMN commant TO comment;

Here we fix a typo in the column name from "commant" to "comment". Ensures accuracy as requirements expand.

Add Foreign Key

Foreign keys enforce cross-table referential integrity:

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)

We realize the orders table should be linked to customers. Adding a foreign key does this to protect data consistency.

Modify Column

Change column data types, widths, nullability as constraints change:

ALTER TABLE customers
MODIFY COLUMN last_name VARCHAR(100) NOT NULL;

We hit a bug allowing NULL last names. Altering the column to disallow NULLs fixes this.

Carefully evolving SQL table schemas via ALTER TABLE is key for managing changing data needs.

Mastering ALTER TABLE gives the confidence to evolve SQL schemas to changing needs, while maintaining existing data integrity. Refer to table change requirements proactively while designing databases.