SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strategy for adding Constraints to legacy database


Strategy for adding Constraints to legacy database

Author
Message
meade.swenson
meade.swenson
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 53
I'm a senior developer working on a mid-sized SQL Server 2005 system (1000 tables) that is several years old. The original implementation did not make use of foreign keys (FK) and unique constraints (UQ), but left it to the developers' to ensure data integrity. As you would suspect, we are plagued with duplicate data in many parts of the system. New management is requiring the use of foreign keys and unique constraints help manage data integrity.

We have scripts to scrub the data to the point where the FKs and UQs can be applied. The problem, however, is the software. Much of the legacy software was not written with such constraints in mind, so it is not uncommon for a process to fail due to a constraint violation, e.g., copying a row and then changing key information.

Here's the question: Does anyone have a strategy for introducing FK and UQ constraints to a database and managing the subsequent software issues?

One possibility: dedicate a developer to the problem, have him/her apply the desired constraints to a local database, exercise the software to test the constraints, and make the required changes when the software fails.

Any suggestions would be greatly appreciated. TIA.

...mcs
Bill Hansen
Bill Hansen
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 336
Is it possible to run a Trace and identify all of the existing code that violates the constraints to be added? If you have a QA server with comprehensive automated scripts for regression testing it might be possible to identify all areas of the code that violate the constraints. If not maybe a sampling of production over a period of time will be sufficient.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search