That rare beast: the one-to-one relationship (1 Viewer)

PhilManchester

New member
Local time
Today, 12:45
Joined
May 12, 2015
Messages
2
After a break of about 6 years I have just started using Access again for a simple project to keep track of items at a bring and buy event. I decided that it might be convenient to store the data about things for sale in two tables, ITEM and COSTS joined be a one-to-one relationship.

There was no problem in creating the tables or the relationship, but during testing I discovered that when I attempted to save a new record in the table ITEM (which uses an autonumber field for its unique identifier) I could not - I got an error message because there was no corresponding record in the table COSTS.

Now I know that I can avoid the problem by combining fields from the two tables into one, but it got me thinking.

How does one create corresponding records with the same identifier in two tables at the same time? How is a one-to-one relationship actually implemented in Access?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:45
Joined
Sep 12, 2006
Messages
15,613
if its one-to-one, then in general its not needed. all the fields can be included in a single table.

itemdescription, cost, sellingprice.

----
if you persist, then create a relationship that designates the item table as the master table. ie right click the join and select items as the "all items table"

Then you can have an item without a cost, but not a cost without an item. Which makes sense.
 

ButtonMoon

Registered User.
Local time
Today, 12:45
Joined
Jun 4, 2012
Messages
304
There was no problem in creating the tables or the relationship, but during testing I discovered that when I attempted to save a new record in the table ITEM (which uses an autonumber field for its unique identifier) I could not - I got an error message because there was no corresponding record in the table COSTS.
You have created the relationship the "wrong way round". It sounds like COSTS should have a foreign key that references ITEM. What you've actually implemented I guess must have a foreign key in ITEM that references COSTS, and that's why you have this problem.

Note that what you are describing here is not "one to one", it's "zero/one to one", meaning that one side of the relationship is optional. I.e. the presence of a row in the COSTS table is optional. In general Access cannot implement truly "one to one" relationships between two tables.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:45
Joined
Sep 12, 2006
Messages
15,613
You have created the relationship the "wrong way round". It sounds like COSTS should have a foreign key that references ITEM. What you've actually implemented I guess must have a foreign key in ITEM that references COSTS, and that's why you have this problem.

Note that what you are describing here is not "one to one", it's "zero/one to one", meaning that one side of the relationship is optional. I.e. the presence of a row in the COSTS table is optional. In general Access cannot implement truly "one to one" relationships between two tables.

Well you can easily achieve one-to-one by having identical/corresponding PK's in each table.

It's rarely necessary though.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:45
Joined
Feb 28, 2001
Messages
26,996
If you actually create the relationship as one-to-one, you will see this. Button, I think you misinterpreted the nature of the beast.

When you have a one-to-one relationship AND have chosen to enforce relational integrity (RI), you MUST have a key on both sides of the 1/1 setup, and you can't load one table at a time if you do that. If you were not enforcing RI, then you would be right that 1/1 behaves like 1/(0 or 1).

Phil, the only way I know to do this is to not enforce RI or not make it a 1/1 relation. You can make it a one-many (which includes the case that ButtonMoon mentioned, the 1/(0 or 1) case). It is also possible to do this with a JOIN query that is updatable, but even here, there is a matter of timing. You can get away with it if you always use the query to update the two tables at once AND have the JOIN written in the way to match the order of checking for RI.

But here is the truth of that approach... if you always have to use a two-way JOIN to update the two tables at once, you might as well make it a single merged table anyway.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:45
Joined
Sep 12, 2006
Messages
15,613
doc_man. one of the options in setting a relationship as well as "enforce RI", is "join type". So you can declare it as a left-join - 1-1 with a master/sub table, and achieve what you want that way.

you then have an item, optionally linked to a single record, either 1-0 or 1-1.


----
where I think something like this might be useful is with a personnel table, say

have a "public section" that all can see, and an optional 1-1 join to a "private section" with details that only selected users can see.
 

Solo712

Registered User.
Local time
Today, 08:45
Joined
Oct 19, 2012
Messages
828
Two observations on the margin:

1) there is no one-to-one relationship in the OP. Relationships can be only between entities. Items are entities; Costs are attributes of Items. It is not even one-to-one as costs for a single item are bound to change in time. An example of a true one-to-one relationship would be 'mother' in a birth certificate table in China: no nulls allowed there; both mother and child are entities; China enforces one-child policy.

2) What the OP suggests is a table "extender". This is sometimes justified if a large number of members do not share certain types of related attributes (eg. married/family info in an HR table of an employee). At any rate, the idea of a shared "Primary Key" strikes me as a barbaric way of doing relational database (, regardless of the obvious issue with RI). There is no reason for it if both sides of the table are truly entities. If you are extending a table, as is the case here, it is clearly a design error to attempt to share the primary key. The dependence of one table on the other is manifest. You cannot speak of an item to a cost. The cost if kept in a separate table (costing history) should have an FK Item ID.

Best,
Jiri
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:45
Joined
Feb 28, 2001
Messages
26,996
Solo, I agree with both your points, though the technical reason for #1 to be true is a bit abstract.

Your point #2 is actually more direct. The 1/1 relationship "muddies the waters" as to what the PK means and it is possible that it violates a normalization rule in the process (having to do with attribute dependence always and only on the PK.) In this case, the technical violation has to do with why an item is in table 1 or table 2, because if BOTH tables are truly based on the same unique keys, the table of residence is an "implied attribute" that does NOT depend on the PK - which denormalizes both tables.
 

Users who are viewing this thread

Top Bottom