Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strategy for adding Constraints to legacy database Expand / Collapse
Author
Message
Posted Monday, January 24, 2011 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 29, 2012 11:00 AM
Points: 1, 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
Post #1052377
Posted Sunday, November 20, 2011 6:56 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 30, 2014 11:19 AM
Points: 565, Visits: 317
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.
Post #1208993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse