SQL ALTER TABLE

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.