0

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?

  • My question is why? If you cannot remove the existing keys why add new ones? If you've tested it, and found the new keys do speed your app dramatically over the composite keys then why are you asking the question? Just implement. Either way, it seems to me you are in the best position to answer your question. – Max Vernon Mar 13 '14 at 1:35
  • I cannot add new keys since the existing ones cannot be removed, but rather, I'm attempting to work around this by adding new columns that could be used in the same manner by another application. – Bernard Mar 13 '14 at 12:37
  • Do you have access to the application source code? – Jon Seigel Mar 14 '14 at 13:33
  • 2
    That's okay; at least you can see the potential queries that could be run against the database. Big advantage. That said, what problem are you trying to solve with this project? Multi-column keys complicate things, yes, but any ORM (for example) system I've seen (that's worth using) can handle that. – Jon Seigel Mar 14 '14 at 16:43
  • 3
    We use DevExpress as well, but only for the reporting components. We looked at using their ORM but discounted it really quickly because it wasn't as mature feature-wise as others. Modifying a legacy database is sometimes completely infeasible, so their argument is ridiculous, IMO. Implementing a modern ORM on a legacy database can be a significant challenge if the ORM doesn't have a full suite of features available. – Jon Seigel Mar 15 '14 at 17:47
1

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.

  • 3
    or throwing out the "modern" application and use one that can handle composite keys. – ypercubeᵀᴹ Mar 14 '14 at 12:29
  • 1
    @ypercube: Most ORMs don't handle composite keys very well, and the one being used by the modern application is no exception. Ideally it could handle them as expected, but it does not. – Bernard Mar 14 '14 at 13:38
  • @Andrew Brennan: In the long run the legacy application will eventually die and should be thrown out, but unfortunately it is still very widely used and therefore still being maintained and enhanced. – Bernard Mar 14 '14 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.