Fixing Foreign Key issues with a legacy database

Question

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:

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.

Answer 1

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

Answer 2

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.