WikiGalaxy
SQL Alter Table
Introduction to SQL ALTER TABLE
Overview:
The SQL ALTER TABLE statement is used to modify an existing database table's structure. It allows you to add, delete, or modify columns in an existing table.
Syntax:
The basic syntax for using the ALTER TABLE statement is as follows:
ALTER TABLE table_name
ADD column_name datatype;
Adding a Column
Example:
To add a new column to an existing table, use the following command:
ALTER TABLE Employees
ADD DateOfBirth DATE;
This command adds a new column named 'DateOfBirth' of type DATE to the 'Employees' table.
Modifying a Column
Example:
To modify the data type of an existing column, use the following command:
ALTER TABLE Employees
MODIFY COLUMN LastName VARCHAR(100);
This command changes the data type of the 'LastName' column to VARCHAR(100) in the 'Employees' table.
Dropping a Column
Example:
To remove a column from a table, use the following command:
ALTER TABLE Employees
DROP COLUMN MiddleName;
This command deletes the 'MiddleName' column from the 'Employees' table.
Renaming a Column
Example:
To rename a column, use the following command:
ALTER TABLE Employees
RENAME COLUMN Address TO Location;
This command renames the 'Address' column to 'Location' in the 'Employees' table.
Adding Constraints
Example:
To add a constraint to a column, use the following command:
ALTER TABLE Employees
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);
This command adds a check constraint to ensure that the 'Age' column has values greater than or equal to 18.
Dropping Constraints
Example:
To drop a constraint from a table, use the following command:
ALTER TABLE Employees
DROP CONSTRAINT CHK_Age;
This command removes the check constraint named 'CHK_Age' from the 'Employees' table.
Renaming a Table
Example:
To rename an existing table, use the following command:
ALTER TABLE Employees
RENAME TO Staff;
This command changes the name of the 'Employees' table to 'Staff'.
