Retro-building Referential Integrity into Tables

[cft]

I have built a database over time, but failed to establish referential integrity at the outset. Is there a method by which to do this after the fact? What should I be looking for in fields, etc that will create problems? Anything that you can provide will be appreciated. Thanks.

 

[The_Doc_Man]

A quick and dirty method is to build a series of JOIN queries that link on the fields to which you wish to establish RI. Do it in a way that allows you to see the main table records even if the translation table (lookup table) has no entry. (In other words, NOT a one-to-one query).

Now make sure that when you do the JOIN, you select at least one field from the lookup table besides the primary key field. Now, to find the records in your main table that would appear to violate RI, make the query show only records for which the non-key field has Len(Nz(non-key-field,"")) = 0. (I am presuming that you have a value field that is never null if the lookup is valid. If this isn't true, make it true.)

For each query based on a JOIN to the field that is a candidate for establishing RI,...

Go fix up the lookup tables by adding entries until the time that, when you run that query, you get back no records.

Once this happens, you can turn on RI for that relationship.

Do the above once for each relationship.

Yes, I know it is tedious. If you are lucky, though, it will go fast.

 

[cft]

Thanks for the detailed level of your response! I will follow this closely and advise the success (or otherwise) I achieve.

 

[cft]

10-4, and thank you again for the prompt and helpful directions!