The ALTER TABLE statement changes a table's structure without removing its data. Common operations include:

• Adding or dropping columns
• Changing data types
• Adding or removing constraints (like NOT NULL or foreign keys)
• Renaming columns or entire tables

Important Tips:

• Always think about how changes affect existing data
• Some databases have different syntax for each ALTER operation
• Test changes in a staging environment or transaction before altering production tables
• Consider the impact on applications using the database

Let's start simple: add a new column called "description" to the "products" table.

Write a query that:

• Uses ALTER TABLE on the "products" table
• Adds a TEXT column named "description"
• Allows null values (no NOT NULL constraint needed)

Remember to double-check your database's column naming conventions before you add new columns.

When you add a column, you can include constraints at the same time to enforce rules on the data.

Now, add a "created_at" column to track when each entry was created.

Write a query that:

• Uses ALTER TABLE on the "products" table
• Adds a TEXT column named "created_at"
• Sets NOT NULL to ensure every row has a value
• Uses DEFAULT CURRENT_TIMESTAMP to store creation times automatically

Tip: When adding a NOT NULL column to an existing table, you must either: 1. Provide a DEFAULT value, or 2. Ensure the table is empty

Some SQL dialects, including SQLite, allow renaming columns right in the ALTER TABLE command.

Rename the "description" column to "product_details".

Write a query that:

• Uses RENAME COLUMN
• Changes "description" to "product_details"

Make sure you only rename columns when you're certain your application code is prepared for the new name.

Tip: After renaming a column:

• Update any views that reference the old column name
• Modify any stored procedures or functions
• Update application code that uses the column

Foreign keys create relationships between tables. Suppose we have a "suppliers" table, and each product references a row in that table.

Add a "supplier_id" column to "products" that points to "suppliers.id".

Write a query that:

• Uses ALTER TABLE on "products"
• Adds an INTEGER column named "supplier_id"
• Establishes a foreign key to "suppliers(id)"

Important considerations when adding foreign keys:

• The referenced column must be unique (usually a primary key)
• Existing data must satisfy the constraint
• Consider the impact on INSERT and UPDATE operations

ALTER TABLE can also rename an entire table. Suppose "products" is now better described as "inventory".

Rename the "products" table to "inventory".

Write a query that:

• Uses RENAME TO
• Changes the table name to "inventory"

Checklist before renaming a table:

• Identify all dependencies (views, triggers, procedures)
• Update application code references
• Consider using a transaction for safety
• Document the change for other developers