January 24, 2011 at 8:01 am
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
November 20, 2011 at 6:56 pm
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply