Keys

[dhstein]

I'm reading the manual, but I'm missing something. First of all, it seems
that I don't need to declare a foreign key - I just have to set up the
relationship between the primary key in one table and another field (the
foreign) key in another table. Second, if my primary key is Autonumber, does
the foreign key need to be a number? Third, do I need to do a "join" when I
do the relationship? Any help would be appreciated. Thanks.

 

[Rick Brandt]

I'm reading the manual, but I'm missing something. First of all, it
seems that I don't need to declare a foreign key - I just have to set up
the relationship between the primary key in one table and another field
(the foreign) key in another table.
Correct.

Second, if my primary key is
Autonumber, does the foreign key need to be a number?

It needs to be a compatible type so with an AutoNumber on the one side a
Long Integer is normally used on the other.

Third, do I need
to do a "join" when I do the relationship?

If you create the relationship in the relationships window then the "GUI"
way to do that is to draw a line similar to a join. That line is really
just a graphical representation of the relationship. Actual "Joins" are
used in queries.

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 17:29:01 -0800, dhstein

Congrats for wanting to do the right thing. In addition to what Rick
said: yes, the FK of an autonumber must be Long Integer. Also, make
sure you enforce the relationships in the Relationship dialog.
Otherwise they are just a line without any implication.

-Tom.
Microsoft Access MVP

 

[dhstein]

On Tue, 25 Nov 2008 17:29:01 -0800, dhstein

Congrats for wanting to do the right thing. In addition to what Rick
said: yes, the FK of an autonumber must be Long Integer. Also, make
sure you enforce the relationships in the Relationship dialog.
Otherwise they are just a line without any implication.

-Tom.
Microsoft Access MVP


Rick, Tom,

Thanks for the responses. A couple of more questions. 1) The only drop
down choice that makes sense is "Number" - I assume that is a long integer.
I'm enforcing referential integrity, but I'm not sure about whether to select
"cascade update related fields" and "cascade delete related records" Also
the "join type" brings up a window with three choices - can you shed any
light on these options? Thanks.

 

[Rick Brandt]

Thanks for the responses. A couple of more questions. 1) The only
drop down choice that makes sense is "Number" - I assume that is a long
integer. I'm enforcing referential integrity, but I'm not sure about
whether to select "cascade update related fields" and "cascade delete
related records" Also the "join type" brings up a window with three
choices - can you shed any light on these options? Thanks.

MS made a REALLY stupid decision years ago and made only one type called
"Number" and used the field size property to distinguish between Integer,
Long Integer, Double, etc.. You need to select Number as the type and
long Integer as the size (near the bottom).

You might want cascade delete. That would mean that if you delete a
record on the parent side of the relationship all child records in the
related table are automatically deleted. Cascade update won't be
necessary since AutoNumber values cannot be changed anyway. In a similar
relationship where the one side was not an AutoNumber then you would have
to decide if you wanted that. I typically do use both.

The join types (I think) only set the default joins that will be auto-
created if you build a query containing the two tables. I never change
that.

 

[Tom van Stiphout]

On Tue, 25 Nov 2008 18:06:03 -0800, dhstein

You first select Number, then the subtype Long Integer.

Cascade Update only make sense if you don't have an autonumber; after
all: you're not updating the autonumber value.
Cascade Delete depends on your requirements. If you delete a Customer,
do you also want her Orders and OrderDetails and Payments to go away?

-Tom.
Microsoft Access MVP

 

[dhstein]

On Tue, 25 Nov 2008 18:06:03 -0800, dhstein

You first select Number, then the subtype Long Integer.

Cascade Update only make sense if you don't have an autonumber; after
all: you're not updating the autonumber value.
Cascade Delete depends on your requirements. If you delete a Customer,
do you also want her Orders and OrderDetails and Payments to go away?

-Tom.
Microsoft Access MVP


Thanks for the responses.