Solved Best practices -- One-to-One table vs keep in same table

[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.

 

[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.