Walkthrough: Modify Database Objects

Article
06/26/2007

In this walkthrough, you use the Transact-SQL (T-SQL) editor to modify the definitions of several database objects in your database project. This process consists of the following steps:

Open a solution that contains a database project.
Add a column to a table. Your customer wants to track the year in which products were introduced. To perform this step, you add a DateAdded column to the Products table.
Add a table, ShipperRating, to track how customers rate the quality of service from the shippers that handle their orders. You also add foreign key relationships and an index.

Prerequisites

This walkthrough assumes that you have completed Walkthrough: Put an Existing Database under Version Control. As a result of that walkthrough, you have a solution that contains a database project named MyNorthwind.

To open the MyNorthwind solution

On the File menu, point to Open, and then click Project/Solution.

The Open Project dialog box appears.
Open the MyNorthwind folder, and double-click MyNorthwind.sln.

The MyNorthwind solution opens and appears in Solution Explorer.
On the View menu click Schema View.

Schema View appears if it was not already visible. It shows all objects that are defined in the database project.
Expand the database project node in Schema View if it is not already expanded.

Next you modify a table definition to add a column to a table.

To add a DateAdded column to the Products table

In Schema View, right-click the Products table, and click Open. You can also double-click the Products table.

The T-SQL editor opens and displays the definition for the Products table.

In the T-SQL editor, modify the definition to add the DateAdded column as the following example shows:

CREATE TABLE [dbo].[Products]
(
[ProductID] [int] NOT NULL IDENTITY(1, 1),
[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[SupplierID] [int] NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0),
[DateAdded] [datetime] NULL
) ON [PRIMARY]

On the File menu, click Save dbo.Products to save your changes.

By default, the file is automatically checked out from source control. If you have modified your source control settings, you are prompted to check out the file.

Next you add a table named ShipperRating to the database project.

To add the ShipperRating table

In Schema View, click the Tables folder.
On the Project menu, click Add New Item. You can also right-click the Tables folder, point to Add, and then click Table.

The Add New Item dialog box appears.
In the Templates list, click Table.
In Name, type ShipperRating, and then click Add.

The ShipperRating table is added to the database project and to source control. The T-SQL editor appears so that you can edit the definition for this table.

In the T-SQL editor, modify the table definition to match the following example:

-- =============================================
-- Create table definition for ShipperRating 
--Contains a rating of a shipper by
--a customer, on a particular date.
--Ratings are from 1-100.
-- =============================================
CREATE TABLE [dbo].[ShipperRating]
(
[ShipperID] [int] NOT NULL,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
[RatingDate] [datetime] NULL,
[Rating] [int] NOT NULL
) ON [PRIMARY]

On the File menu, click Save dbo.ShipperRating to save your changes.

Next you add a foreign key to the ShipperRating table.

To add the foreign key to the ShipperRating table

In Schema View, right-click the ShipperRating table, point to Add, and then click Foreign Key.

The Add New Item dialog box appears with the Foreign Key template already highlighted.
In Name, type FK_ShipperRating_Shippers, and then click Add.

The FK_ShipperRating_Shippers foreign key is added to the ShipperRating table in your project. The T-SQL editor appears so that you can edit the definition for the foreign keys.

Note

The default definition for the foreign key references column_1 in tablename instead of a real table and column. Therefore, the icon for FK_ShipperRating_Shippers in Schema View shows the error icon (a red circle that contains a white "x"). Also, an error appears in the Error List window to indicate that you have an invalid database object definition. This behavior is expected. The file in Solution Explorer that contains the object definition does not show an error icon.

In the T-SQL editor, modify the foreign key definition to match the following example:

ALTER TABLE [dbo].[ShipperRating]
ADD CONSTRAINT [FK_ShipperRating_Shippers] 
FOREIGN KEY ([ShipperID])
REFERENCES [dbo].[Shippers] ([ShipperID])

On the File menu, click Save dbo.FK_ShipperRating_Shippers to save your changes. The error icon is replaced by the regular icon for a foreign key constraint because the definition is now valid.

In the final step, you add an index to the ShipperRating table.

To add an index to the ShipperRating table

In Schema View, click the Indexes folder.
On the Project menu, click Add New Item. You can also right-click the Indexes folder, point to Add, and then click Index.
In the Templates list, click Index if it is not already highlighted.
In Name, type ShipperRatingDate, and then click Add.

The ShipperRatingDate index is added to the ShipperRating table in your project. The T-SQL editor appears so that you can edit the definition for the index.

Note

The default definition for the index references column_1 instead of a real column name. Therefore, the icon for ShipperRatingDate in Solution Explorer shows the error icon (a red circle that contains a white "x"). Also an error appears in the Error List window to indicate that you have an invalid database object definition. This behavior is expected.
In the T-SQL editor, modify the index definition to match the following example:
```
-- =============================================
-- Create index on RatingDate column in 
--the ShipperRating table.
-- =============================================
CREATE INDEX [ShipperRatingDate]
ON [dbo].[ShipperRating]
(RatingDate)
```
On the File menu, click Save ShipperRatingDate to save your changes. The error icon is replaced by the regular icon for an index because the definition is now valid.

To check your changes into version control

In Solution Explorer click the MyNorthwind database.
On the File menu, point to Source Control, and then click Check In.
Check in your files by following the procedures for your source control plug-in. For more information, see either Working with Team Foundation Source Control or Performing Basic Visual SourceSafe Tasks.

The database project changes have now been checked in and are now available to other team members.

Next Steps

After you modify the offline representation of this database, you must build and deploy those changes to the database server. To build and deploy your changes, see Walkthrough: Deploy Changes to an Existing Version-controlled Database.

Walkthrough: Modify Database Objects

Prerequisites

To open the MyNorthwind solution

To add a DateAdded column to the Products table

To add the ShipperRating table

To add the foreign key to the ShipperRating table

To add an index to the ShipperRating table

To check your changes into version control

Next Steps

See Also

Tasks

Concepts

Other Resources

Additional resources