Content of Foreign Key

[ffisher]

Hi, Please bear with me as I'm new at this.In the old example of
orders/parts/order_details, the order_details table is the junction
table between the other two. Primary key in the Orders Table is the
Order_ID and the priamry key in the parts table is the Parts_ID.
Okay.
Now the junction table has three fields. Parts_ID, Orders_ID and
Quantity.
I'm okay with all this so far.
What I dont understand is if the primary keys are set to autonumber in
the parent tables, do I have to create new fields in the junction table
with the same name as the primary keys in the other tables and format
them to number and then add all the autonumber data in manually?
To re-phrase the question, do the foreign keys have the same data
content as the primary keys? What content can I expect to see in these
foreign keys when I view the junction table in Datasheet view?
Maybe I have to somehow copy the data or the these fields somehow from
the parent tables to the child table?

 

[Albert D.Kallal]

To re-phrase the question, do the foreign keys have the same data

content as the primary keys? What content can I expect to see in these
foreign keys when I view the junction table in Datasheet view?
Maybe I have to somehow copy the data or the these fields somehow from
the parent tables to the child table?

Yes, you got it well right...

Also, while many people use the term "junction table", from a ease of
thinking, and relation point of view, it is must better to just think in
terms of a table.

For example, I have a order table, let call it tblOrders.

I also have a table called tblParts.

For each tblOrder, I need to "list" the parts needed. So, lets make a table
called tblOrdersListOfParts.

Calling something a "junction" table kind of confuses the issue. And, in
fact, tblPartgs is really a lookup table in the sense that you can delete,
or add many orders, and you can add, or delete many tblOrdersListOfParts
records, but in no case do you actually delete anything, or add anything in
the parts table.

The only reason why we got a pats table is because we cant remember the
parts by memory. However, the only real enforced relationship here is that a
between tblOrders, and tblOrdersListOfParts. we are free to add, or delete
tblOrdrs records, but they never effect tblParts. In fact, we are free to
delete records in tblOrderListOfParts, and again we never delete, or do
anything to tblParts.

So, I got a table called "orders"
For each order, I might have "many" parts, so, we need a table called
tblOrdersListOfPartrs.

And, our description of parts is also needed, so we got a table called
parts.

Notice I never started talking about confusing junction tables here..

What I dont understand is if the primary keys are set to autonumber in
the parent tables, do I have to create new fields in the junction table
with the same name as the primary keys in the other tables and format
them to number and then add all the autonumber data in manually?

Yes. Note that the names of the fields do NOT need to be the same as the
parent table. In fact, since each table "defaults" to "id" for the primary
key autonubmer, then why not adopt that as a standard for all tables. That
means every table you have will always have a autonumber field called "id".
You never have to write this down, or "know" that is the case, since all
pk's will be this.

Now, for the child tables, you may, or may not need a primary key, but, if
you do, then again, you use "id", and autonumber. However, for those fields
that you were just asking about, they MUST be defined as the SAME data type
(eg: long number = autonumber), but of course can't be a autonumber field.
These are regular "long number" fields. So:

and then add all the autonumber data in manually?

Yes, you do. However, if you build a form, and a sub-form, them ms-access
will put in the values in the fields for you (you have to set the link
"master" and link "child" fields so the form can know to do this for you.

So, we would build a "main" form on our tables called "tblOrders"

We would build a sub-form for the "many" side. In this case tblListOfOrdres.
That sub-form would have three fields


id tblOrders_ID tblParts_id

The "id" is not of much use, but might come in handy in the future.

tblOrders_ID is the field used to relate back to the tblOrders. This field
is often called a foreign key.
tblParts_id is the field used to hold the parts id.

So, we could build a sub-form, and really we only need to display the
tblParts_ID field. A continues sub-form would make this look quite nice.

However, since we can't remember the parts id, then you simply could/would
use the wizard to build a combo box for the tblParts. The combo box would be
based on the tblParts, and would store the "id" to the field tblParts_id.
Note how elimination of talking about junction tables, and weird stuff makes
this whole concept a lot easer. Note also how for the tblparts_id, that the
USER IS going to set this value, not ms-access. We use a combo box here, so
the user dn't have to reember the actaull "id" used.

However, for the tblOrdres_ID, there is no need for the user to type in a
id, and our designs + forms should handle this for the user.

Note also how we name our fields that hold primary keys from other tables. I
use tablename + primary key name, and you get

tblOrders_ID

This means my poor little brain never has to make up a pk, or fk name, as I
adopted some naming scheme.

 

[John Vinson]

Hi, Please bear with me as I'm new at this.In the old example of
orders/parts/order_details, the order_details table is the junction
table between the other two. Primary key in the Orders Table is the
Order_ID and the priamry key in the parts table is the Parts_ID.
Okay.
Now the junction table has three fields. Parts_ID, Orders_ID and
Quantity.
I'm okay with all this so far.
What I dont understand is if the primary keys are set to autonumber in
the parent tables, do I have to create new fields in the junction table
with the same name as the primary keys in the other tables and format

The name of the child field is irrelevant. The datatype of the foreign
key must match that of the parent table's Primary Key; if that is
Autonumber, then the Parts_ID and Orders_ID fields in the junction
table must be Long INtegers.

them to number and then add all the autonumber data in manually?

You don't need to even SEE them, much less type them in. If you have a
Form based on Orders, with a subform based on OrderDetails, you'ld use
OrderID as the master/child link field; this will display all order
details for that order, and automatically fill in the currently
selected mainform OrderID when you create a new detail line. On the
subform you'll have a Combo Box *displaying* the product name, but
*storing* the product ID. Again, it'll be put into the table, but you
don't need to show it to the user.

To re-phrase the question, do the foreign keys have the same data
content as the primary keys?
Yes.

What content can I expect to see in these
foreign keys when I view the junction table in Datasheet view?

Numbers. If Order 3178 includes orders for Products 92, 114 and 513,
you'ld have rows like

3178; 92; 1
3178; 114; 5
3178; 513; 1

Maybe I have to somehow copy the data or the these fields somehow from
the parent tables to the child table?

That's what the Form does for you automatically.

Table datasheets are useful primarily for design and debugging.
Especially for multitable operations like this, you should use Forms
to interact with the tables.

See the Northwind sample database Orders form - it does exactly what
you're describing.


John W. Vinson[MVP]

 

[ffisher]

Thanks to Albert and John,
Reading through both of your replies it seems the approach is to create
a form to fill in the information from the PK into the FK.

First I will add data to the tables then create my relationships and
then create the form.
I assume this is the correct order of working.
Thanks.
ffisher

 

[BruceM]

I'm going to jump in here and hope that I don't rile the waters. The
correct order is to create the structure (including relationships), then add
the data. Relational databases start with tables and relationships. Forms
are the user interface.

To stress a point that has already been mentioned, if you are using an
autonumber PK (primary key) in Orders, put the field into the Orders table,
put a corresponding Number field into the Order Details table, create the
relationship between the two fields, then forget about it. Every time you
use a form to create a record for an Order it will have a unique autonumber
PK. Every record you create in the corresponding subform will have that
same number in the related field.

Do what you wish for naming fields, tables, etc., but it helps to be
consistent. I would call an Orders table tblOrders. A query based on that
table would be qryOrders, the form would be frmOrders, and so forth. With a
nod to Albert, who has provided endless valuable advice to myself and others
in this forum, I prefer to stay away from generic field names. I would tend
to use OrderID as the name of the PK field in tblOrders, and would also use
OrderID as the name of the related field in tblOrderDetails.
tblOrderDetails may have its own ID field, which needs to be a different
name than any other field in that table. I find it easier to create
relationships between fields with the same name. That's just how my brain
seems to be wired.

Using the naming scheme I have outlined, if the PK for an Order record is
123, every correspondig record in the Order Details will have 123 in the
OrderID field. Every record in OrderDetails with 123 in the OrderID (child)
field will be related to one and only one record in tblOrders. One common
example of how this works is with payroll. If you have an EmployeeID number
where you work, every payroll record for you will contain that number.
Those records will be associated with you and only you, because nobody else
has that same EmployeeID number. If your name changes, the old records will
be carried forward since they are associated with an unchanging number.

A word on junction tables, which are used in specialized situations. If you
are keeping track of students and courses, each student will take many
courses, and each course will be attended by many students. Therefore the
relationship between Students and Courses is many-to-many. You cannot
create a many-to-many relationship directly between two tables, so a third
table is needed to resolve the relationship. This third table (I will call
it tblEnrollment) is related to both the Students and Courses tables. I
don't think you need to concern yourself with that in your situation. Each
OrderDetail is associated with only one Order. With Parts you may want to
store the Part information in the OrderDetails table (the price, at least)
rather than linking to the Parts record, so there is probably no
many-to-many relationship. I mention this because junction table is a term
in common usage, and not to argue with Albert's point that the term can
become confusing. It is, after all, just another table.

 

[ffisher]

Thanks Brucem for your reply. It makes more sense but I have one last
question to make it clear for me. I found the quote below in the
Micorsoft Newsgroups:

"Each child record maintains a copy of its parent's Primary
Key as a long integer (called a Foreign Key - more confusing because
there is no help or reference about Foreign Keys). If you use
Referential Integrity and draw a line in the Relationships window
between the Primary Key in tblCustomer to the ForeignKey in tblOrder
then the system will take care of writing in the Foreign Key value for
you."

The guy saying that the system will enter the values into the foreign
key automatically.

If I understand you correctly I should set up the tables and
relationships BEFORE entering data in the tables (either via direct
table enteries or via a form). What I dont understand is that if I
enter data into the parent tables then at what stage should I see the
primary keys values from the parent table showing in the foreign key of
the child table? Do the values only appear if the data has been entered
into the parent table via a form? At what stage can I expect to see
these values in the foreign key and after completing what process?

 

[BruceM]

Responses inline.

Thanks Brucem for your reply. It makes more sense but I have one last
question to make it clear for me. I found the quote below in the
Micorsoft Newsgroups:

"Each child record maintains a copy of its parent's Primary
Key as a long integer (called a Foreign Key - more confusing because
there is no help or reference about Foreign Keys)."

In table design view a field is defined as Autonumber, Number, Date, Text,
etc. If tblOrders has a primary key (PK) field defined as Autonumber, the
related field (the foreign key, or FK) in tblOrderDetails is defined as
Number. If the PK field is defined as anything other than Autonumber, the
FK field must be identically defined.

"If you use
Referential Integrity and draw a line in the Relationships window
between the Primary Key in tblCustomer to the ForeignKey in tblOrder
then the system will take care of writing in the Foreign Key value for
you."

The quote describes a fairly typical system in which there are customers,
each of whom may have several orders, each of which may contain several
items. The relationship between Orders and OrderDetails works exactly the
same way as the described relationship between Customers and Orders.
First, design the tables. Using the Autonumber system, in tblOrders define
the OrderID field as Autonumber and PK. Add other fields that are specific
to an order (OrderDate, etc.). In tblOrderDetails, define a PK field
(autonumber is OK). I will call it OrderDetailsID. Create a CustomerID
field, and define its data type as Number. Again, add other fields as
needed (Quantity, PartNumber, Price, etc.). Now that you have both table,
open the Relationships window (Tools > Relationships). Add both tables.
Drag CustomerID from one table on top of CustomerID in the other table, and
let go. In the dialog box that appears, click Enforce Referential
Integrity, and click the Create button. You should see a line connecting
the two fields, with an infinity sign on one side and a "1" on the other.

The guy saying that the system will enter the values into the foreign
key automatically.

Create a form based on tblOrders (I will call it frmOrders) and another
based on tblOrderDetails (fsubOrderDetails). Set the default view of
fsubOrderDetails to Continuous. To do this, with the form open in design
view (and no individual control selected) you would click View > Properties,
and click the Format tab. You can also see the from's properties by click
the little square in the very top left. In form design view, drag the icon
for fsubOrderDetails onto frmOrders. Switch to Form view. You can now
enter Order information in the main form, and OrderDetails in the subform.
As you enter each new OrderDetail, the PK will be written automatically to
the FK field.

If I understand you correctly I should set up the tables and
relationships BEFORE entering data in the tables (either via direct
table enteries or via a form).

Use the form. Viewing tables directly should be part of the design and
development process only. Users should not work directly with tables.
There are too many ways to mess things up.

What I dont understand is that if I
enter data into the parent tables then at what stage should I see the
primary keys values from the parent table showing in the foreign key of
the child table?

As soon as you start to type in the OrderDetail record.

Do the values only appear if the data has been entered
into the parent table via a form?

There needs to be a parent (Order) before there can be children (Order
Details).