The ALTER command in SQL Server is used to modify the structure of an existing database object, such as a table, view, or column. The ALTER command allows you to add, modify, or drop columns, constraints, and other table elements. Here are various uses of the ALTER command with appropriate examples:
Altering a Table
1. Adding a Column
To add a new column to an existing table:
ALTER TABLE Employees
ADD Email NVARCHAR(100);
- This command adds a new column named Email to the Employees table.
2. Modifying a Column
To change the data type or other attributes of an existing column:
ALTER TABLE Employees
ALTER COLUMN Email NVARCHAR(150) NOT NULL;
- This command changes the Email column to a larger size (150 characters) and makes it NOT NULL.
Get Hands-On with SQL Server - Enroll in Our Developer Course at TrainingHub.io!
3. Dropping a Column
To remove an existing column from a table:
ALTER TABLE Employees
DROP COLUMN Email;
- This command removes the Email column from the Employees table.
Altering Constraints
1. Adding a CHECK Constraint
To add a new CHECK constraint to an existing table:
ALTER TABLE Employees
ADD CONSTRAINT CHK_Age CHECK (Age >= 18);
- This command adds a CHECK constraint to ensure the Age column is at least 18.
2. Adding a DEFAULT Constraint
To add a new DEFAULT constraint to an existing column:
ALTER TABLE Employees
ADD CONSTRAINT DF_HireDate DEFAULT GETDATE() FOR HireDate;
- This command adds a DEFAULT constraint to the HireDate column, setting the default value to the current date if no value is provided.
3. Dropping a Constraint
To remove an existing constraint:
ALTER TABLE Employees
DROP CONSTRAINT CHK_Age;
- This command removes the CHK_Age constraint from the Employees table.
Renaming a Table
To rename an existing table:
EXEC sp_rename 'Employees', 'Staff';
- This command renames the Employees table to Staff.
Renaming a Column
To rename an existing column:
EXEC sp_rename 'Employees.Email', 'EmailAddress', 'COLUMN';
- This command renames the Email column to EmailAddress in the Employees table.
TrainingHub.io is your gateway to professional success with our premier Data Analyst courses available in Toronto.
Example Scenario
Suppose we have an existing table named Orders:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
Amount DECIMAL(10, 2)
);
We want to make the following changes:
1. Add a new column OrderStatus.
2. Change the Amount column to allow for larger amounts.
3. Add a CHECK constraint to ensure Amount is non-negative.
4. Add a DEFAULT constraint to set OrderStatus to 'Pending'.
5. Rename the CustomerID column to ClientID.
Implementing the Changes
1. Adding a Column
ALTER TABLE Orders
ADD OrderStatus NVARCHAR(20);
2. Modifying a Column
ALTER TABLE Orders
ALTER COLUMN Amount DECIMAL(15, 2);
3. Adding a CHECK Constraint
ALTER TABLE Orders
ADD CONSTRAINT CHK_Amount CHECK (Amount >= 0);
4. Adding a DEFAULT Constraint
ALTER TABLE Orders
ADD CONSTRAINT DF_OrderStatus DEFAULT 'Pending' FOR OrderStatus;
5. Renaming a Column
EXEC sp_rename 'Orders.CustomerID', 'ClientID', 'COLUMN';
By using the ALTER command effectively, you can manage and modify your database schema to meet evolving business requirements and ensure data integrity.
Make Your SQL Server Knowledge Unstoppable - Sign Up for Our Course! TrainingHub.io, known as the best IT training institute in Canada, offers a diverse range of courses to cater to all learning needs.
Suggested blogs
Recent blogs
