ALTER Command

05 June 2024

|
3 min read
Blog Image

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.

Recent blogs

Featured Image
GROUP BY Clause

21 June 2024

|
3 min read
Featured Image
CASE Statement

15 June 2024

|
4 min read
Featured Image
ORDER BY Clause

10 June 2024

|
3 min read