Walkthrough: Modify Database Objects

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 Schema 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

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

   The Open Project dialog box appears.

2. Open the MyNorthwind folder, and double-click MyNorthwind.sln.

   The MyNorthwind solution opens and appears in Solution Explorer.

3. On the View menu, click Database Schema View.

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

4. 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

1. 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.

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

   CREATE TABLE [dbo].[Products] (
       [ProductID]       INT           IDENTITY (1, 1) NOT NULL,
       [ProductName]     NVARCHAR (40) NOT NULL,
       [SupplierID]      INT           NULL,
       [CategoryID]      INT           NULL,
       [QuantityPerUnit] NVARCHAR (20) NULL,
       [UnitPrice]       MONEY         NULL,
       [UnitsInStock]    SMALLINT      NULL,
       [UnitsOnOrder]    SMALLINT      NULL,
       [ReorderLevel]    SMALLINT      NULL,
       [Discontinued]    BIT           NOT NULL,
       [DateAdded] [datetime] NULL
   );
   

   When you modify the object definition, you check out the file that contains that definition from version control.

3. 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

1. In Schema View, click the Tables folder.

2. 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.

3. In the Templates list, click Table.

4. 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.

5. 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]
   

6. 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

1. 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.

2. 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.

3. 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])
   

4. 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

1. In Schema View, click the Indexes folder.

2. On the Project menu, click Add New Item. You can also right-click the Indexes folder, point to Add, and then click Index.

3. In the Templates list, click Index if it is not already highlighted.

4. 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.

5. 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

1. In Solution Explorer click the MyNorthwind database.

2. On the View menu, point to Other Windows, and click Pending Changes.

   The Pending Changes window appears so that you can review all of the files that you have added or modified.

3. (optional) Specify a check-in comment.

4. Click Check In.

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

   Note

   The steps in this procedure are written as though you are using Team Foundation version control. If you are not using Team Foundation version control, you must instead follow the procedures for your version control system.

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.

See Also

Tasks

Walkthrough: Put an Existing Database Schema Under Version Control

Walkthrough: Create and Deploy a New Version-Controlled Database

Concepts

Scenario: Create and Modify Database and Server Objects

Terminology Overview of Database Edition

Other Resources

Building and Deploying Version-controlled Databases