One to One relationship and referential integrity (1 Viewer)

[BiigJiim]

Hi,

I have a Acc2010 database with a main table tbl_Case and half a dozen or so other tables each of which has a 1-1 relationship with tbl_Case. Each table has a field called CaseID which is the primary key. (The reason I haven't got this as one huge table is that the number of fields would be enormous and not all sections of the case record will be completed, so easier to have each section as a separate table.)

My question is this:
I want to set referential integrity so that I can add a record to tbl_Case without needing a related record in each of the other tables. BUT I want to prevent each of the other tables having a record which does not have a related record in tbl_Case. Basically the tbl_Case record will always be populated first and then each of the other tables may or may not have a record populated. I have set up relationships with referential integrity as shown in the attached jpeg, with tbl_Case on the left side of the window.

This seems to allow me to do what I need. But I am not sure why it allows tbl_Case records, but not records in the related tables when the relationship is 1-1. Have I done it correctly? Join properties on each relationship are set to 1 (inner join).

Thanks for any input,
Jim

 

[jdraw]

Do you have a test database with this structure you can post?
I've seen 1 to 1 tables, but primarily for a single table for security/need to know/restricted access purposes. I have not seen half a dozen or so other tables each of which has a 1-1 relationship with the main table.

 

[The_Doc_Man]

You can always make the relationship 1/many from your main to the split tables and that would allow the 1/0 case (i.e. add to main without having to add to sub.) You could then make that (technically, FK) field the PK in each sub-table, which would keep the records unique. The manually created relationship CAN be something other than 1/1 even if both fields are unique in the main and sub tables. Doing it that way would allow you to have a "directional" relational integrity.

 

[BiigJiim]

Do you have a test database with this structure you can post?
I've seen 1 to 1 tables, but primarily for a single table for security/need to know/restricted access purposes. I have not seen half a dozen or so other tables each of which has a 1-1 relationship with the main table.

I don't on here unfortunately. Its being developed on a machine with no internet access and no way of mailing out, for security purposes. But trust me, the structure of the tables is correct.

 

[theDBguy]

Hi Jim. I would second @jdraw's request. I would also be interested to see what "those other fields" are in each section. Thanks.

 

[The_Doc_Man]

I have seen that kind of thing with the U.S. Navy because (a) different security for certain things due to HIPAA, National Secrets Act, and Privacy Act leading to different regulatory issues, and (b) if you don't need all of the data at once because it is divisible into at least THREE separate cases (we had as many as 10), you don't need the stuff from parts B, C, D, and E if you are only processing part A. The JOIN query for Main+A works with smaller records AND you "churn" smaller records with updates. If you merge the smaller tables into one larger table, then (for Access at least) EVERY RECORD churns potentially hundreds of bytes when in fact you might only have needed to update about sixty or seventy bytes.

From a complexity standpoint, I'm against it, but if there is any kind of regulatory security involved, I fully understand it. If BigJiim's project involves the kind of security that needs an isolated machine, then I'm betting that not less than three or four government regulations are in effect.

 

[Pat Hartman]

True 1-1 relationships are extremely rare and many people make them to split off fields as you are doing but they should instead normalize the table to reduce the number of fields correctly. We can help with that if you'll post the existing schema.

You don't say what error you are getting but the problem is likely that you have defined the PK of the -1 side tables as autonumber. They should NOT be autonumbers, they should be defined as long integer as all other FK's are.