Fixing Foreign Key issues with a legacy database

Tools: SQL2000/5/8, .NET 3.5, C#

I have come across an application that has two tables. In “code” the tables look like this:

  • How to pivot unknown number of columns & no aggregate in SQL Server?
  • Return rows even if record does not exist LEFT OUTER JOIN
  • Sql Server Pivot with multiple columns from a record
  • Concat groups in SQL Server
  • How to combine a linq query dynamically?
  • SSIS is running extremely slow in designer view
  • TABLE1 (1——N) TABLE2

    So Table1 (T1) has an Id: IdT1 and Table2 (T2) has its id (IdT2) and a foreign key t2.IdT1

    This 1.N relationship is code enforced to some degree. There were no FKs in the DB whatsoever. (Whoever designed this didn’t add constraints nor anything similar).

    Problem is, I have found that the application uses the IdT1 in TABLE2 for (correctly) storing the referenced row on TABLE1, but also uses Zero (0) for a special case.

    So I have (in Table2) something along these lines:

    IDt2 IdT1 OtherFields
    1    1    x
    2    1    x
    3    5    x
    4    0    x
    5    3    x
    6    0    x
    …
    

    As you can see in rows 4 and 6 the FK points to an inexistent row in TABLE1. The software works because it has (a lot) of places where it “tracks” this with an IF statement or similar. Changing that right now is not really a good idea (I wouldn’t want to touch code that “works and I didn’t write” right now), unless it’s the only way.

    Now I’m modifying other aspects of the application and I need the Database to have the FKs (we’re autogenerating code with a template and if the FKs are not there, certain things won’t be generated).

    Given the above scenario, is there any way I can create a FK that doesn’t check constraints “ever”? Will this be a “problem” (considering that the App has been working for over 5 years with this thing of Id = 0 in the FK)? Do you have any suggestions? Tks.

  • Debugging in SSIS
  • EF 6 - How to correctly perform parallel queries
  • UUID collision risk using different algorithms
  • Using CLR UDTs as SQL Server stored procedure parameters
  • Could not load file or assembly Microsoft.SqlServer.SqlClrProvider on production ASP.net site, where is it?
  • Code to validate SQL Scripts
  • 2 Solutions collect form web for “Fixing Foreign Key issues with a legacy database”

    Generally, The only purpose of a Foreign Key Constraint is to check if the Foreign Key exists in the Primary Key table. If you don’t want it to do that, why do you want it in there at all?

    One solution is to add a Magic Row in the Primary Key table with a PK of zero, then add the FK constraint. This is not the recommended solution from a purist approach, but given the constraints you specify in your question, may be the best solution.

    If you want to create a FK and you don’t want it to check existing data you can use WITH NOCHECK…but again you are just defeating the whole purpose of a FK in this case. also be aware that constraints defined WITH NOCHECK are not considered by the query optimizer

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.