Unanswered: Referential Integrity for one-to-one relationship
I am running into several weird problems with a new database i'm creating. i'm attempting to solve the problems one at a time, and the very first one has me stumped...
i have two tables with a one-to-one relationship. one of the tables will contain private protected information related to the public records in the other table. i don't want the user doing data entry to have to input anything into the private field, nor do i want it to even appear on the data entry form anywhere. there is no data in either table yet.
in my relationships, i specified "enforce referential integrity," and when i try to turn on the "cascade update related fields" checkbox i get the following error message:
"invalid field definition '[primary key of public table]' in definition of index or relationship"
the help file for the error message tells me that it's either spelled wrong (it's not) or it's an OLE object or memo field (it's not).
so two questions--1) will "cascade update related fields" automatically create a new record in the private table for every new record created in the public one? and 2) if so, why can't i turn it on?
currently, if i try to make a new record in the public table, it tells me a related record is required in the private table, even if i have referential integrity enforced without cascading updates to related fields.
Why are you not putting everything in the same table and giving the user a form to work on instead of poking around in the tables? Hide the database window, disable allowbypasskey and compile it to an mde, that's more than enough security for your casual user. The user who can get around the above mentioned security will undoubtedly have no issue getting around storing protected information in a seperate table.
You can't really "cascade" anything in a 1-1 relationship because it's not logical to do so. Which is the master and which is the detail? Logically, there is no definition.
yeah i see your point about the lack of logic in cascading updates in a 1-to-1... and not having the protected table at all may me a viable solution--though not my first choice for how to deal with it...
i'm really frustrated that nothing in this database seems to be working the way i expected it to--and i believe it's because i've enabled referential integrity for all relationships. when i create a query with more than one of the related tables, it dissallows the creation of new records.
I came across this error by been an idiot.. just in case anybody else is also
Make sure your data types are correct.
i.e. if you have a field of type "autonumber" be sure it isn't trying to cascade updates to another field that is also of type "autonumber". It won't let you for obvious reasons. I found I made this mistake because access makes the first field of type autonumber autobloodymagicly. you need to go into design view and move it to "number".. or the relevent type.