SQL ALTER

← Previous Next →

The ALTER statement in SQL is used to modify the structure of an existing database object, such as a table. It allows you to add, delete, or modify columns, constraints, and other aspects of database schema.

Common Uses of the ALTER Command:
1. Add Columns
2. Modify Columns
3. Drop Columns
4. Rename Columns or Tables
5. Add or Drop Constraints

Example:

-- Add the status column with a default value
ALTER TABLE Orders
ADD status VARCHAR(20) DEFAULT 'Pending';

-- Rename the total_amount column to order_total
ALTER TABLE Orders
RENAME COLUMN total_amount TO order_total;

-- Modify the customer_id column to BIGINT
ALTER TABLE Orders
MODIFY customer_id BIGINT;

-- Add a constraint to ensure the order_total is positive
ALTER TABLE Orders
ADD CONSTRAINT chk_order_total CHECK (order_total > 0);


Key Points:
   • ALTER is for modifying the structure of existing tables, not the data. You use DML commands (e.g., INSERT, UPDATE, DELETE) to manipulate data.
   • Add and remove columns: You can dynamically expand or simplify your table schema using ALTER.
   • Renaming columns or tables: ALTER can also help you change names, making it easier to maintain clarity in your schema.
   • Modifying data types: It’s possible to change column data types, but you should be cautious about data loss or truncation, especially when reducing the column length or changing incompatible data types.
   • Constraints: You can add or drop constraints using ALTER to enforce data integrity rules in your database.

← Previous Next →
Topics