Suppose you have an SQL Server database used by a legacy application that has many tables with composite primary key columns (and thus, composite foreign key columns as well). Instead of composite primary key columns, a single primary key column is desired for use by other, more-modern applications, but since the legacy application cannot be changed, neither can these composite primary key columns.
My initial solution involves the following:
- Adding a new identity column to each table with composite primary key columns, as well as adding unique non-clustered indexes to these identity columns.
- Adding new nullable integer columns to each table with composite foreign key columns, as well as foreign key constraints to the corresponding new identity columns and non-clustered indexes to these integer columns.
- Adding AFTER INSERT/UPDATE triggers to these tables to update these new integer columns when new rows are inserted or existing rows are updated.
Given the above scenario, is my solution an effective way to modify these SQL Server database tables with existing composite primary key columns to allow for a single unique column to be introduced to allow for other applications to use instead? Are there any storage or performance issues to consider?
Best Answer
Adding new columns to tables might break the legacy app, because DML might be expecting a certain number of columns of certain types in a certain order in each table.
There aren't really any elegant solutions for this kind of problem. You can make new tables for the new apps and duplicate the data with triggers, but that creates unneccessary complexity and of course duplication. You can make new keys in new tables and use views to present the data to the new apps as one table, but then you're stuck with only using procedures for inserts and updates from the new apps.
In the long run you will probably be better off throwing out the legacy app.