Refactoring A Database After Deployment



By:

Overview

At some point, all databases eventually need to be updated where new objects are added/dropped or database code requires modification.

Explanation

Our table has a primary key but it's missing a UNIQUE constraint. Let's go to the Schema View and add a UNIQUE constraint by ClubName and City to our FootBallClub table:

add a UNIQUE constraint by ClubName and City to our FootBallClub table

The familiar Add New Item screen appears and we name our constraint:

The familiar Add New Item screen appears

Once we click Add, we can modify the produced template as follows:

we can modify the produced template as follows

We re-build and re-deploy the database project:

We re-build and re-deploy the database project

The output window shows that our constraint was added successfully and that we didn't suffer and seed data primary key violations because our post deployment script was made re-runnable:

we didn't suffer and seed data primary key violations

Examining SQL Server Management Studio again, we verify our UNIQUE constraint has been added to the database:

Examining SQL Server Management Studio again, we verify our UNIQUE constraint has been added to the database

Last Update: 9/10/2011






Comments For This Article





download


get free sql tips
agree to terms


Learn more about SQL Server tools