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





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools