In SQL, the ALTER command is used to modify the structure of an existing database object such as a table, column, or constraint. Unlike the CREATE command, which creates new objects, ALTER changes the definition of objects that already exist in the database.
The ALTER command is commonly used to:
- Add new columns
- Modify existing columns
- Rename columns or tables
- Drop columns
- Add or remove constraints
Syntax of ALTER Command
ALTER TABLE table_name
action;The action depends on what change you want to make.
1. ADD a New Column
To add a new column to an existing table:
ALTER TABLE students
ADD age INT;Explanation:
This command adds a new column named age with data type INT to the students table.
2. MODIFY a Column
To change the data type or size of an existing column:
ALTER TABLE students
ALTER COLUMN name TYPE VARCHAR(100);Explanation:
This modifies the name column to allow up to 100 characters.
Note: Syntax may slightly vary between databases like MySQL, PostgreSQL, and Oracle.
3. RENAME a Column
To rename an existing column:
ALTER TABLE students
RENAME COLUMN name TO full_name;Explanation:
This changes the column name from name to full_name.
4. DROP a Column
To remove a column from a table:
ALTER TABLE students
DROP COLUMN age;Explanation:
This deletes the age column permanently from the students table.
5. ADD a Constraint
To add a constraint like UNIQUE or PRIMARY KEY:
ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);Explanation:
This ensures that all values in the email column are unique.
6. DROP a Constraint
To remove an existing constraint:
ALTER TABLE students
DROP CONSTRAINT unique_email;Explanation:
This removes the unique constraint from the email column.
Advantages of ALTER Command
- Helps in updating database structure without losing data
- Allows flexibility in database design
- Useful for maintaining and upgrading databases