Referential Integrity

[RT]

I've encountered a puzzling problem. I'm testing database referential
integrity. I've read where the program won't permit entry of a record in
the "many" table without a record being entered in the 'one" side table of a
relationship.

I downloaded the MSAccess template for their Time and Billing database for
Access 2002/2003. When I open the table "Time Card Hours", I can enter a
record in this table.

Can someone tell me why. It's important, because I'm building a database
similar to this. (trying to learn).

Thanks.

 

[bz]

I've encountered a puzzling problem. I'm testing database referential
integrity. I've read where the program won't permit entry of a record in
the "many" table without a record being entered in the 'one" side table of
a relationship.

I downloaded the MSAccess template for their Time and Billing database for
Access 2002/2003. When I open the table "Time Card Hours", I can enter
a record in this table.

Can someone tell me why. It's important, because I'm building a database
similar to this. (trying to learn).

Thanks.

This may mean "Referential Integrity" is NOT enforced in the tables.

 

[bz]

I've encountered a puzzling problem. I'm testing database referential
integrity. I've read where the program won't permit entry of a record in
the "many" table without a record being entered in the 'one" side table of
a relationship.

I downloaded the MSAccess template for their Time and Billing database for
Access 2002/2003. When I open the table "Time Card Hours", I can enter
a record in this table.

Can someone tell me why. It's important, because I'm building a database
similar to this. (trying to learn).

Thanks.

Oh I got it. Sorry I didn't look at the actual tables to see what was in
there.

The reason is that "referential integrity" only works against the Key. The
"Time Card Hours" table doesn't seem to have the... huh... actual key. The
key for this table should be a combination of "ProjectID" + "TimeCardID" +
"TimeCardDetailID" together; not just "TimeCardDetailID".

If you set up these keys for this table, you will not be able to type
anything to this table.

 

[RT]

Yes, that is exactly what is happening. I'm developing a new database based
on a version that I built in Access 97. I noticed the problem and
downloaded the MS template to test and the same thing happened. I thought
maybe it was my DB, but Not. When I tried it in the template, the same
action occurred.

If I create a new DB, RI is enforced. I've tried converting my original DB
to 2002/2003 format. No go. I can still add a record in the "many" table.
That's when I downloaded the template. Same thing occurred. Why?? It
would definitely be good to know.

Thanks for answering.

 

[RT]

Thanks. You're right. I see it now.

It's a join table so it must have at least 2 key fields. I think maybe it
should be the keys from the Project table (ProjectID) and the key from the
Time Card table (TimeCardID). Like Northwind ?? I think that would insure
not entering orphan records?

I've never created 3 primary keys for a table but I'll try it.

 

[bz]

Actually, if you want to use only one column as the key, you can do this
too. But you need to add a middle-man table to refer the relationship
between those tables.

Instead of linking the Project, Time Card, and the Time Card Detail tables
directly, you can add a new table that only store the keys from each of
these tables. I don't know if this is a sound database design or not
though.

 

[Dirk Goldgar]

In

I've encountered a puzzling problem. I'm testing database
referential integrity. I've read where the program won't permit
entry of a record in the "many" table without a record being entered
in the 'one" side table of a relationship.

I downloaded the MSAccess template for their Time and Billing
database for Access 2002/2003. When I open the table "Time Card
Hours", I can enter a record in this table.

Can someone tell me why. It's important, because I'm building a
database similar to this. (trying to learn).

Thanks.

I haven't looked at that template and table, but bear in mind that
referential integrity can be enforced on a field, but if the field also
allows Nulls (in table design view, its Required property is set to No),
you are allowed to add records with Null in that field. You just can't
add records with a non-null value in that field that is not also in the
referenced "one-side" table. Essentially this means, "If I give you a
value for this field, it must be found in the referenced table, but if I
don't know the value, I can still add the record."

If you don't want this behavior, set the field's Required property to
Yes.