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?
