Learn how the ALTER TABLE
statement in SQL streamlines your database management by simplifying modifications to a table's structure without affecting its data. This feature allows easy modification of table columns, constraints, and indexes.
Why Use ALTER TABLE?
When you need to change your database table, such as to accommodate new data types or business requirements, you can use ALTER TABLE
to seamlessly modify tables rather than recreating them from scratch.
Syntax
The basic syntax for SQL ALTER TABLE is as follows:
ALTER TABLE table_name
ADD column_name data_type [column_constraint],
MODIFY column_name data_type [column_constraint],
DROP column_name;
Here, The table_name
is the name of the table you want to modify. You can use the ADD
keyword to add a new column to the table, the MODIFY
keyword to modify an existing column, and the DROP
keyword to delete a column. The column_name
is the column name you want to add, modify, or delete. The data_type
is the column's data type, and the column_constraint
is any constraint you wish to apply to the column.
How to Add a New Column
To add a new column to an existing table, use the following syntax:
ALTER TABLE table_name
ADD column_name data_type [column_constraint];
For example, to add a new column named email
of data type VARCHAR(50)
to the students
table, you would use the following statement:
ALTER TABLE students
ADD email VARCHAR(50);
Modify an Existing Column
To modify an existing column in an existing table, use the following syntax:
ALTER TABLE table_name
ALTER column_name data_type [column_constraint];
For example, to modify the data type of the salary
column in the employees
table from INTEGER
to DECIMAL
, you would use the following statement:
ALTER TABLE employees
ALTER COLUMN salary DECIMAL;
Please note that ALTER
is used in SQL Server and MS Access, while MODIFY COLUMN
is used in MySQL and Oracle.
How to Delete a Column
To delete a column from an existing table, use the following syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
For example, to delete the price
column from the orders
table, you would use the following statement:
ALTER TABLE orders
DROP COLUMN price;
Renaming a Table or Column
To rename a table or column, use the following syntax:
ALTER TABLE old_table_name
RENAME TO new_table_name;
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;
For example, to rename the employees
table to staff
and the age column to years, you would use the following statements:
ALTER TABLE employees
RENAME TO staff;
ALTER TABLE staff
RENAME COLUMN age TO years;
Adding Constraints
Tables can have constraints to enforce rules on data. For example, a unique constraint ensures that no two rows in a table have the same value in a particular column.
To add a constraint to a table, use the following syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
For example, to add a unique constraint on the email
column in the employees
table, you would use the following statement:
ALTER TABLE employees
ADD CONSTRAINT UNIQUE_EMAIL UNIQUE (email);
Dropping Constraints
To drop a constraint from a table, use the following syntax:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
For example, to drop the UNIQUE_EMAIL
constraint that we just added, you would use the following statement:
ALTER TABLE employees
DROP CONSTRAINT UNIQUE_EMAIL;
Conclusion
You've now grasped the essentials of using SQL's ALTER TABLE
statement to manage database tables effectively. Whether you're adding new columns or enforcing data integrity through constraints, ALTER TABLE
has got you covered.