Walkthrough: Modify Database Objects

  • Article

In this walkthrough, you use the Transact-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 Product table in the Production schema.

  • Add a table, Purchasing.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 MyAdvWorks.

To open the MyAdvWorks solution

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

    The Open Project dialog box appears.

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

    The MyAdvWorks 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 Product table

  1. In Schema View, expand the Production folder, expand the Tables folder, right-click the Product table, and click Open.

    As an alternative, you can also double-click the Product table to open it.

    The Transact-SQL editor opens and displays the definition for the Product table.

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

    CREATE TABLE [Production].[Product] (
    [ProductID]             INT              IDENTITY (1, 1) NOT NULL,
    [Name]                  [dbo].[Name]     NOT NULL,
    [ProductNumber]         NVARCHAR (25)    NOT NULL,
    [MakeFlag]              [dbo].[Flag]     NOT NULL,
    [FinishedGoodsFlag]     [dbo].[Flag]     NOT NULL,
    [Color]                 NVARCHAR (15)    NULL,
    [SafetyStockLevel]      SMALLINT         NOT NULL,
    [ReorderPoint]          SMALLINT         NOT NULL,
    [StandardCost]          MONEY            NOT NULL,
    [ListPrice]             MONEY            NOT NULL,
    [Size]                  NVARCHAR (5)     NULL,
    [SizeUnitMeasureCode]   NCHAR (3)        NULL,
    [WeightUnitMeasureCode] NCHAR (3)        NULL,
    [Weight]                DECIMAL (8, 2)   NULL,
    [DaysToManufacture]     INT              NOT NULL,
    [ProductLine]           NCHAR (2)        NULL,
    [Class]                 NCHAR (2)        NULL,
    [Style]                 NCHAR (2)        NULL,
    [ProductSubcategoryID]  INT              NULL,
    [ProductModelID]        INT              NULL,
    [DateAdded]             DATETIME         NOT NULL,
    [SellStartDate]         DATETIME         NOT NULL,
    [SellEndDate]           DATETIME         NULL,
    [DiscontinuedDate]      DATETIME         NULL,
    [rowguid]               UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [ModifiedDate]          DATETIME         NOT NULL
);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Products sold or used in the manfacturing of sold products.', @level0type = N'SCHEMA', @level0name = N'Production', @level1type = N'TABLE', @level1name = N'Product';

    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 Product.table.sql to save your changes.

    By default, the file is automatically checked out from source control. If you have modified your source control settings, you might be prompted to check out the file, depending on the configuration of your version control settings.

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

To add the ShipperRating table

  1. In Schema View, expand the Purchasing folder, and 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 Transact-SQL editor appears so that you can edit the definition for this table.

  5. In the Transact-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 [Purchasing].[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 ShipperRating.table.sql 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 Transact-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 Transact-SQL editor, modify the foreign key definition to match the following example:

    ALTER TABLE [Purchasing].[ShipperRating]
ADD CONSTRAINT [FK_ShipperRating_Shippers] 
FOREIGN KEY ([ShipperID])
REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID])

  4. On the File menu, click Save ShipperRating.FK_ShipperRating_Shippers.fkey.sql 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 Transact-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 Transact-SQL editor, modify the index definition to match the following example:

    -- =============================================
-- Create index on RatingDate column in 
--the ShipperRating table.
-- =============================================
CREATE INDEX [ShipperRatingDate]
ON [Purchasing].[ShipperRating]
(RatingDate)

    On the File menu, click Save ShipperRating.ShipperRatingDate.index.sql 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. 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.

    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.

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

  3. Click Check In.

    The changes to the database project are checked in and 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.

See Also

Tasks

Walkthrough: Put an Existing Database Schema Under Version Control

Walkthrough: Create and Deploy a New Version-Controlled Database

Concepts

Writing and Changing Database Code

Build and Deploy Databases to an Isolated Development Environment

Refactor Database Code and Data