Referential integrity rules

[mhart]

Hi,
I am redesigning a database that I first created in 1997. Back then I knew nothing about database design, and I know very little more now--there are just a lot more resources available.

I have imported only the 17 tables into Access 2010 (I wanted to start from scratch with the queries and forms). The original Relationships did not include referential integrity, but I want to include it in the new design if at all possible. I've been able to create referential integrity for most of the tables by "cleaning up" some of the records in the primary table, but I have some tables that won't accept referential integrity with the primary table. The error message states, "data in the primary table violates referential integrity rules." (The primary table has approximately 5,000 records.)

There are no obvious problem records in any of the tables. What should I be looking for in the records to correct the problem?

Am I putting too much importance into referential integrity? The database has worked great for nearly 15 years without it.

Thanks.

[TheShabz]

A violation of referential integrity rules most likely means you have a foreign key that has no primary key in the "parent" table. To see if that's the case, remove any relationship between the two tables and do a query with an outer join from the table with the foreign key to the table with the primary key and look for results where the primary key table Is Null.

For example:

tblprimary
primaryID (PK)

tblSecondary
SecondaryID (PK)
primaryID

SELECT * FROM tblSecondary Left Join tblprimary on tblprimary.primaryID = tblSecondary.primaryID WHERE tblprimary.primaryID Is Null

[mhart]

It worked! Thank you so much. Through the process I realized that when I originally designed the database I left the default value in the number fields as 0, which caused the problems. Obviously, I've changed that in the new tables.

Thanks again!