The ALTER command in SQL is used to modify an existing database object, such as a table. It allows you to add, delete, or modify columns, as well as change data types, rename columns, and apply constraints to the existing structure of a table. The ALTER command provides flexibility to make structural changes without deleting and recreating the table.
The general syntax for using the ALTER command to modify a table is:
sqlALTER TABLE table_name MODIFY column_name data_type;
In this syntax:
To add a new column to an existing table, use the ADD keyword with the ALTER command.
sqlALTER TABLE table_name ADD column_name data_type;
Example: Adding a phone_number column to the employees table.
sqlALTER TABLE employees ADD phone_number VARCHAR(15);
To delete a column from an existing table, use the DROP COLUMN keyword with the ALTER command.
sqlALTER TABLE table_name DROP COLUMN column_name;
Example: Dropping the phone_number column from the employees table.
sqlALTER TABLE employees DROP COLUMN phone_number;
To change the data type of an existing column, use the MODIFY keyword with the ALTER command.
sqlALTER TABLE table_name MODIFY column_name new_data_type;
Example: Changing the salary column’s data type in the employees table to DECIMAL(12, 2).
sqlALTER TABLE employees MODIFY salary DECIMAL(12, 2);
To rename a column in a table, use the RENAME COLUMN keyword with the ALTER command (supported by most databases).
sqlALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
Example: Renaming the emp_name column to employee_name in the employees table.
sqlALTER TABLE employees RENAME COLUMN emp_name TO employee_name;
You can also use the ALTER command to add constraints to columns, such as NOT NULL, UNIQUE, or CHECK constraints.
sqlALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
Example: Adding a CHECK constraint to ensure that the salary column in the employees table has values greater than zero.
sqlALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
To remove a constraint from a column, use the DROP CONSTRAINT keyword with the ALTER command.
sqlALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example: Dropping the chk_salary constraint from the employees table.
sqlALTER TABLE employees DROP CONSTRAINT chk_salary;
The ALTER command can also change other table properties, like renaming the table.
To rename an existing table, use the RENAME TO command.
sqlALTER TABLE old_table_name RENAME TO new_table_name;
Example: Renaming the employees table to staff.
sqlALTER TABLE employees RENAME TO staff;
The ALTER command in SQL is highly versatile and allows database administrators and developers to make changes to database structures without losing existing data. With the ALTER command, you can add, delete, or modify columns, add constraints, and even rename tables and columns, providing a flexible way to adapt database structures as requirements change.
Copyrights © 2024 letsupdateskills All rights reserved