Is it always bad to NOT enforce referential integrity?

[Access_Novice]

I have two tables, 'Timesheet' and 'Paid.' I have a compound index on both tables consisting of name & date & taskCode set to unique values. When I try to link them in relationships, and enforce referential integrity, I get an error message saying: data in the paid table violates referential integrity rules. For example there may be records relating to an employee in the related table, but no record for the employee in the primary table. I guess a way around this is to not enforce referential integrity. Is this bad or is this ok? Keep in mind, I have timesheet data with hours and paid data with hours and I am trying to pair them up, to account for what has been paid.

Additionally (and I think this ties into the problem above), I have some records in the 'timesheet' table that do not appear in the 'Paid' table and vice versa. I also have some records that appear on both tables.

I do not want to show only matches, or all records from only one table. I want to show all records from both tables so that I can see matches and mismatches both ways.

I thought about doing a compound index on just name & date, in order to be less selective, but that would result in repeating values in both tables so I don't think that would be an option.

[June7]

No.

Apparently, those two tables do not have a parent/child dependent relationship. Why would records be in Paid if there is no associated Timesheet record?

Many experienced developers don't even bother to set up relationships and instead rely on design of forms and code to control data integrity and restricting user access to the tables and queries.

[ItsMe]

If you plan on letting other developers have access to your tables via linking or ODBC, you will want to consider constraints at the table level. Otherwise, I prefer maintaining Referential Integrity and managing Constraints via VBA.

[Access_Novice]

No.

Apparently, those two tables do not have a parent/child dependent relationship. Why would records be in Paid if there is no associated Timesheet record

But I am able to relate them by using a compound index, and they are definitely related because timesheet hours incurred are eventually paid and I'm trying to reconcile them.

The data for the timesheets table comes from my companies system. The data for the Paid table, comes from the clients system. So the only common denominators I have to work with are name, date and taskCode.

Under Edit Relationships, I can show only matches or all records from one table, and matches from the other. Is there a way to show all records from both tables (matches and mismatches)? There has got to be a way to do this, because I need to show everything that is out there.

[June7]

If you want these tables to have referential integrity then need to clean up the child table and eliminate orphans or add parent records to the other table before setting the relationship.

Do Find Unmatched query to locate the orphans. There is a query wizard to help.

[Access_Novice]

If you want these tables to have referential integrity then need to clean up the child table and eliminate orphans or add parent records to the other table before setting the relationship.

Do Find Unmatched query to locate the orphans. There is a query wizard to help.

Good suggestion. You have been very helpful. Thank you.

[ButtonMoon]

But I am able to relate them by using a compound index, and they are definitely related because timesheet hours incurred are eventually paid and I'm trying to reconcile them.

The problem here is that "relationships" (in the Access sense) have nothing to do with referential integrity. The thing called a relationship in the Access UI is actually a fairly pointless feature for predefining a type of join between tables in the database. It's something that didn't ought to exist in any relational database. Unfortunately perhaps, to enforce referential integrity through the Access UI you first have to create a relationship and then check the "enforce" option. If you are inclined to try it you can bypass this "feature" by using SQL DDL statements in a query window - but not surprisingly, many people will find that too much trouble.

My recommendation: don't create relationships unless you intend to enforce referential integrity (RI). If you do want to enforce RI then it is better to do it in the database rather than in a form or application. Doing it in the database means you create the constraint once and it applies everywhere. You don't have to maintain the rule separately in every application and you don't need to worry about any data getting into the table that doesn't satisfy the RI constraint.

[Access_Novice]

The problem here is that "relationships" (in the Access sense) have nothing to do with referential integrity. The thing called a relationship in the Access UI is actually a fairly pointless feature for predefining a type of join between tables in the database. It's something that didn't ought to exist in any relational database. Unfortunately perhaps, to enforce referential integrity through the Access UI you first have to create a relationship and then check the "enforce" option. If you are inclined to try it you can bypass this "feature" by using SQL DDL statements in a query window - but not surprisingly, many people will find that too much trouble.

My recommendation: don't create relationships unless you intend to enforce referential integrity (RI). If you do want to enforce RI then it is better to do it in the database rather than in a form or application. Doing it in the database means you create the constraint once and it applies everywhere. You don't have to maintain the rule separately in every application and you don't need to worry about any data getting into the table that doesn't satisfy the RI constraint.

You gave a lot of interesting insight. Thank you. I was always under the impression that if you have multiple tables, that each table had to be related to at least one other table. I appreciate your insight.

[June7]

There likely are relationships but setting them in the Relationship Builder is not required.