Please help: edit relationship type

C

cassidy

I can't seem to do what should be a simple Access operation: create a
one-to-MANY relationship.

Following the directions, I drag the primary key
field from table A to the matching foreign key field in table B. The
edit relationships window appears and says this is a one-to-ONE
relationship, with no way that I can see to change the type. What am
I missing?

Thanks very much for any assistance.
 
K

Ken Sheridan

It sounds like the foreign key field is indexed uniquely (no duplicates).
Change this to a non-unique index (duplicates allowed). If the primary key
is an autonumber field then the foreign key should not be an autonumber, but
a straightforward long integer number data type.

Ken Sheridan
Stafford, England
 
C

cassidy

It sounds like the foreign key field is indexed uniquely (no duplicates).
Change this to a non-unique index (duplicates allowed). If the primary key
is an autonumber field then the foreign key should not be an autonumber, but
a straightforward long integer number data type.
Click to expand...
That fixed it--thanks very much. May I also ask a couple of
followups?

....is the converse true? i.e., if I want Access to create a one-to-
one relationship, then the foreign key should be a unique index (no
duplicates allowed)?

....and the relationships window does not show any designations on the
diagram links (the infinity symbol for many, the numeral 1 for one).
Shouldn't these show up, and how do I get Access to produce them?

Again, thank you for your help.
 
K

Ken Sheridan

Yes, a one-to-one relationship requires both columns to be indexed uniquely.
Be careful about creating such relationships inappropriately, however. A
one-to-one relationship is generally used to model a Type/Sub-type scenario.
In this the primary key of the sub-type is also a foreign key which
references the primary key of the (super) type. Chris Date in 'An
Introduction to Database Systems' gives the example of Employees with
sub-type Programmers and sub-types of this, Application Programmers and
System Programmers. The primary key in each case is EmployeeID (Note that
this can only be an autonumber in Employees though). This type of model is
characterised by each sub-type sharing all attributes of its (super) type,
but not those of other sub-types. Don't be tempted to create a one-to-one
relationship simply because you feel a table has too many columns; that is
more likely to indicate it contains redundancy and needs decomposing into
tables related one-to-many, or one-to-many-to-one, the latter modelling two
entity types related many-to-many via a third table (sometimes called a
'junction' table – in the sample Northwind database for example the
OrderDetails table models a many-to-many relationship type between Orders and
Products).

When you create a on-to-one relationship the line between the tables in the
relationship window does not show any symbols, even though the relationship
still has a direction (a sub-type references its (super) type, not vice
versa). The same is true of indeterminate relationships, but they are
usually incorrect and arise from neither column being indexed uniquely. I
don't think you can change this appearance. If a one-to-many relationship is
correctly set up then the infinity symbol and the 1 should show.

Ken Sheridan
Stafford,
 
C

cassidy

Thanks very much again. I did check my design and realize that it
contains only one-to-many and many-to-many (using junction tables);
however, your description of when one-to-one is appropriate makes that
situation more identifiable for me.

It turned out that the symbols on the link hadn't appeared because I
had not yet checked the "enforce referential integrity" boxes.

This had been vexing me for days as I looked through books and online
for what I was doing wrong. Thanks for such a complete and helpful
answer.
marge
 
A

a a r o n . k e m p f

I genrally think that one to one relationships are under-used.

they are a great way to partition data (vertically) and can
dramatically increase performance.

-Aaron
 
K

Ken Sheridan

I have to confess that I'd never noticed that they don't show if referential
integrity is not enforced. In most cases one would do so of course, though
the enforcement of cascade deletes does need thinking about; e.g. you might
want to delete all orders for a customer if that customer is deleted, but you
would probably not want to delete all customers in a city if that city record
is deleted. On the contrary you'd be more likely to want deletion of a city
to be prevented if there are customers in that city; enforcing referential
integrity without enforcing referential deletes would ensure this of course.

Referential updates are of course unnecessary if an autonumber is used as
the primary key of the referenced table, but if a 'natural' key (where the
values are guaranteed to be unique, as with US states for instance, but not
with values like city names which can be duplicated) is used then one would
normally enforce them. Its unlikely that something like the name of a state
would change, but its theoretically possible, and its theoretical
possibilities which have to be catered for.

Ken Sheridan
Stafford, England
 
A

a a r o n . k e m p f

I thought that they illustrated this by putting the key symbols at the
end of the relationship.

With keys = enforced
WIthout keys = not enforced.

Anyways-- Access can't enforce cross-database DRI.. so it's worthless
at best

-Aaron
 
A

a a r o n . k e m p f

Or to 'split' the table so that different depts that update data at
the same grain- can have better performance with updates, deletes,
inserts, etc

it's useful for saving space

I'll probably create a new one-to-one twice a month for the next year;
I hope.

-aaron
 
C

Clif McIrvin

Or to 'split' the table so that different depts that update data at
the same grain- can have better performance with updates, deletes,
inserts, etc
Click to expand...

Aaron -- that is an interesting suggestion. If I'm following you
correctly, two different users could be editing the same logical
record simultaneously without conflict as long as each user was in
fact in a different linked table in the one-to-one relationship.

it's useful for saving space
Click to expand...

? I'm not follwing this one. How does a one-to-one save space? Do you
perhaps have in mind a set of fields that in some cases may all be
null, so the related record is never created? But don't DB engines
only consume storage for data that actually exists?
 
A

a a r o n . k e m p f

it saves space by allowing 'empty cells' to not be present.
It's kindof a hack for normalization..

but I definitely am 'more on fire for one to one' than I ever have
been in my life.

I think that there are performance savings, space savings; security
benefits; I can think of a couple good solid reasons to have a one to
one.

-Aaron
 
A

a a r o n . k e m p f

How can Access support cross-database RI?

Please explain; oh great con-artist

-Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

newbie can't edit relationship type... 1
No Unique Index... 2
Keys 6
creating a one-to-many relationship 1
Connection points on relationship diagram 3
How to Delete a Relationship in VBA 3
Duplicate values in the index, primary key, or relationship messag 2
Cascade Delete Relationship Question 2

Top