Solved Best practices -- One-to-One table vs keep in same table (1 Viewer)

[Pat Hartman]

I'm not sure why anyone is even entertaining the idea of a 1-1 relationship when clearly this is either 1-m or m-m. Can't get a clear answer from the OP on the actual relationship. All we know is that Rep is its own entity, and therefore 1-1 is off the table.

 

[isladogs]

I'm not sure why anyone is even entertaining the idea of a 1-1 relationship when clearly this is either 1-m or m-m. Can't get a clear answer from the OP on the actual relationship. All we know is that Rep is its own entity, and therefore 1-1 is off the table.

Whether or not this is 1:m or m:m wasn't the point I was responding to.
My comments were purely about the incorrect information given concerning RI & 1:1 relationships

 

[Pat Hartman]

@isladogs I wasn't arguing with that. In fact, I pointed out the error back in post #8. Maybe you didn't see that although I didn't go into the "mutual" comment.

 

[KitaYama]

@The_Doc_Man
I wrote a reply to posts #5 & #15 earlier today but forgot to click Post Reply...so here is a slightly modified version of my original reply

As @ebs17 has rightly pointed out, referential integrity can certainly be set for 1:1 relationships including cascade update/delete

For example:
View attachment 106729

In fact, adding both cascades is particularly important for 1:1 relationships
It ensures that records can be added to / deleted from either table without any issues

All of the above is absolutely standard behaviour

I don't even understand what this phrase could possibly mean

By definition, referential integrity applies to both tables in the relationship

I have a lengthy 3-part article on this topic

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

@isladogs I'm trying to create a 1:1 relationship between the following tables by draging one PK over the other.
I'm receiving the following error.
How can I add a 1:1 relation?

Thank you.

 

[isladogs]

Do you have identical values in the 2 PK fields? If not, you can't set RI retrospectively as I'm sure you are aware.

Does it work without specifying cascades?

EDIT: Try changing the ProductPK field from Autonumber to Number or ShortText

 

[The_Doc_Man]

OK, guys - I know I make mistakes, but I am absolutely certain that when I created a 1/1 with properly unique keys, I was unable to write a query to insert a new record because whichever side of the table I tried to insert first, the other one blocked me for RI reasons, "no matching record in related table" or words to that effect. It was for a Navy database so I can't reproduce so easily but I am NOT hallucinating and have a clear memory that the problem was an INSERT INTO of one table of a pair of 1/1 tables. If something has been done to change that behavior, well and good. I'll state that I HAVE seen it happen and then back away from it.

 

[Pat Hartman]

The "ONE" side table has an autonumber PK, the "other" table has a long integer. You MUST insert into the "ONE" side first. Then the "other" side just works like a many table. Access makes the 1-1 designation because you are joining PK to PK. If you join PK to data field, the 1-m designation shows. Access NEVER naturally creates a m-m.

 

[KitaYama]

Do you have identical values in the 2 PK fields? If not, you can't set RI retrospectively as I'm sure you are aware.

Does it work without specifying cascades?

EDIT: Try changing the ProductPK field from Autonumber to Number or ShortText

Thanks for your help.

Both tables are empty. I just created two sample table and tried to join them.

Without cascade it works. I can add the 1:1 relation.

If I change only one of the PKs from AutoNumber to Number I receive no error and the relation is made.


So as a result, is it safe if I think that in a 1:1 relation, one of the PKs MUST be number?

thank you again.


Edit : Seems that @Pat Hartman has already answered my question.
Thanks to all.

 

[KitaYama]

OK, guys - I know I make mistakes, but I am absolutely certain that when I created a 1/1 with properly unique keys, I was unable to write a query to insert a new record because whichever side of the table I tried to insert first, the other one blocked me for RI reasons, "no matching record in related table" or words to that effect. It was for a Navy database so I can't reproduce so easily but I am NOT hallucinating and have a clear memory that the problem was an INSERT INTO of one table of a pair of 1/1 tables. If something has been done to change that behavior, well and good. I'll state that I HAVE seen it happen and then back away from it.

@The_Doc_Man
With the design @Pat Hartman & @isladogs explained, I'm able to insert records to the table with autonumber key, without adding records in the table with long interger key.

BUT, if I try to insert data to the table with long integer key first, I receive the error you're talking about.
Adding record to the table with autonumber key has no problem and no error at all.

 

[Pat Hartman]

So as a result, is it safe if I think that in a 1:1 relation, one of the PKs MUST be number?

No - only ONE of them may be an autonumber. In that case the other MUST be a long integer.

Both can be strings or both can be numbers. It is the autonumber that dictates what MUST be the "master" in the relationship. Usually one side of the relationship is optional. Therefore, it is the side with the required fields that must be the "master:"

Access, and probably all the other RDBMS' will always assume a "master". I'm guessing it will be the left table in the relationship.

 

[KitaYama]

No - only ONE of them may be an autonumber. In that case the other MUST be a long integer.

Both can be strings or both can be numbers. It is the autonumber that dictates what MUST be the "master" in the relationship. Usually one side of the relationship is optional. Therefore, it is the side with the required fields that must be the "master:"

Access, and probably all the other RDBMS' will always assume a "master". I'm guessing it will be the left table in the relationship.

@Pat Hartman Got it.
Thank you.