Welcome to Lesson 8 of the TSQL Tutorial. In this lesson we will cover alter. I hope you enjoy the lesson.
Select Statements
ALTER
The ALTER
statement is used to modify an existing objects, such as adding or
dropping columns in an existing table.
Usage:
ALTER TABLE tablename
ADD columnname datatype
Example: Adding a column called "State" to the employees table.
ALTER TABLE employees
ADD State varchar (2)
If we select all columns from the employee database we should see the States column.
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
City
|
State
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
Seattle
|
|
|
2
|
Fuller
|
Andrew
|
CEO
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
Tacoma
|
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
Kirkland
|
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
Redmond
|
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
London
|
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
London
|
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
Seattle
|
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
London
|
|
|
10
|
Smith
|
John
|
Accountant
|
1/1/1970
|
2/2/2005
|
123 Main
St
|
Denver
|
|
Example 2: deleting a column called "City" from the employees table.
ALTER TABLE employees
DROP COLUMN City
If we select all columns from the employee database again, we no longer see the City column.
|
EmployeeID
|
LastName
|
FirstName
|
Title
|
BirthDate
|
HireDate
|
Address
|
State
|
|
1
|
Davolio
|
Nancy
|
Sales
Representative
|
12/8/1948
|
5/1/1992
|
507 - 20th
Ave. E.
|
|
|
2
|
Fuller
|
Andrew
|
CEO
|
2/19/1952
|
8/14/1992
|
908 W.
Capital Way
|
|
|
3
|
Leverling
|
Janet
|
Sales
Representative
|
8/30/1963
|
4/1/1992
|
722 Moss
Bay Blvd.
|
|
|
4
|
Peacock
|
Margaret
|
Sales
Representative
|
9/19/1937
|
5/3/1993
|
4110 Old
Redmond Rd.
|
|
|
5
|
Buchanan
|
Steven
|
Sales
Manager
|
3/4/1955
|
10/17/1993
|
14 Garrett
Hill
|
|
|
6
|
Suyama
|
Michael
|
Sales
Representative
|
7/2/1963
|
10/17/1993
|
123 Miner
Rd.
|
|
|
8
|
Callahan
|
Laura
|
Inside
Sales Coordinator
|
1/9/1958
|
3/5/1994
|
4726 -
11th Ave. N.E.
|
|
|
9
|
Dodsworth
|
Anne
|
Sales
Representative
|
1/27/1966
|
11/15/1994
|
7
Houndstooth Rd.
|
|
|
10
|
Smith
|
John
|
Accountant
|
1/1/1970
|
2/2/2005
|
123 Main
St
|
|
ALTER can also be used to add constraints to a table. The script
below adds a special constraint called a Primary Key to the
EmployeeID column. A Primary Key is a column in a table whose
values uniquely identify the rows in the table.
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY CLUSTERED (EmployeeID)