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.