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:

  • SQL - Insert NULL into DateTime
  • Change default db to Master db in SQL Server
  • SQL query to get records
  • How do I address sudden loss of connection to SQL Azure in my Azure role?
  • creating a report of how many times something occured
  • SQL Server linked server performance
  • 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.

  • Sync Framework with SQL DB's: Getting Started
  • No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'.
  • asp.net Dynamic Data Site and SQL Server image datatype
  • Programmatically getting SQL Cluster Virtual Name
  • Optimal storage of data structure for fast lookup and persistence
  • Set Locale of a SQL Server 2005
  • 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.