How do you create a composite key that can be referenced?

[bws93222]

I have 3 tables:
OrdersTable (OrdersTable_ID, [Foreign Key from OrderDetailsTable],....)
OrderDetailsTable (OrdersTable_ID, Product_ID...)
ProductsTable (Product_ID, ProdDesc....)

The Primary Key in the OrderDetailsTable is a composite of OrdersTable_ID
and ProductsTable_ID. I know how to create the composite by selecting both
fields and clicking the 'Primary Key' button but how then do I create a
reference between the OrdersTable and the OrderDetailsTable primary/composite
key? (Do I need to use scripting and a junction table to avoid a circular
reference or is there an easier way?) Thx.

 

[Stefan Hoffmann]

hi,

I know how to create the composite by selecting both
fields and clicking the 'Primary Key' button but how then do I create a
reference between the OrdersTable and the OrderDetailsTable primary/composite
key?

Master: FieldA, FieldB, FieldC
PK on (FieldA, FieldB)

Child: FieldA, FieldB, FieldC, FieldD
PK on (FieldA)

To assign a relationship with referential integrity between these two
tables you must use two field in the child table for the foreign key,
e.g. FieldB and FieldC. So that

Master.PK(FieldA, FieldB) -> Child.FK(FieldB, FieldC)

The child fields must have the same data type as their mapped master fields:

dataType(Master.FieldA) = dataType(Child.FieldB)
dataType(Master.FieldB) = dataType(Child.FieldC)

In the relationship design window simply select the two master tables
fields and drag them to the child window on the first field. The
relationship editor displays the rows for the field assignments. Choose
the correct fields and you're done.


mfG
--> stefan <--

 

[Armen Stein]

I have 3 tables:
OrdersTable (OrdersTable_ID, [Foreign Key from OrderDetailsTable],....)
OrderDetailsTable (OrdersTable_ID, Product_ID...)
ProductsTable (Product_ID, ProdDesc....)

The Primary Key in the OrderDetailsTable is a composite of OrdersTable_ID
and ProductsTable_ID. I know how to create the composite by selecting both
fields and clicking the 'Primary Key' button but how then do I create a
reference between the OrdersTable and the OrderDetailsTable primary/composite
key? (Do I need to use scripting and a junction table to avoid a circular
reference or is there an easier way?) Thx.

First, I don't see why you have a foreign key in your OrdersTable. It
shouldn't be there.

I would advise against making a composite primary key for Order
Details. It makes your relationships more complex. If each table has
a single unique primary key (like an AutoNumber), you're fine.

If your goal is to prevent duplicates of the same Product on an Order,
then you can enforce a unique index on those two keys together. You
don't need a primary key to do that.

Now finally, to actually answer your question. You just drag the
individualy fields to each their counterpart in the other table in the
Relationships window. I'm not sure why you would have a circular
reference here. I think maybe the issue I mentioned in my first
paragraph is causing your confusion.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[bws93222]

Thank you both--Thats answers my question. And, yes, the foreign key in my
orders table was unnecessary and that's what threw me off on the wrong foot.
(FYI: my goal in using a composite key was curiosity to learn how it's
done...now that I know, I'll probably have no use for it.)--
bws93222

I have 3 tables:
OrdersTable (OrdersTable_ID, [Foreign Key from OrderDetailsTable],....)
OrderDetailsTable (OrdersTable_ID, Product_ID...)
ProductsTable (Product_ID, ProdDesc....)

The Primary Key in the OrderDetailsTable is a composite of OrdersTable_ID
and ProductsTable_ID. I know how to create the composite by selecting both
fields and clicking the 'Primary Key' button but how then do I create a
reference between the OrdersTable and the OrderDetailsTable primary/composite
key? (Do I need to use scripting and a junction table to avoid a circular
reference or is there an easier way?) Thx.

First, I don't see why you have a foreign key in your OrdersTable. It
shouldn't be there.

I would advise against making a composite primary key for Order
Details. It makes your relationships more complex. If each table has
a single unique primary key (like an AutoNumber), you're fine.

If your goal is to prevent duplicates of the same Product on an Order,
then you can enforce a unique index on those two keys together. You
don't need a primary key to do that.

Now finally, to actually answer your question. You just drag the
individualy fields to each their counterpart in the other table in the
Relationships window. I'm not sure why you would have a circular
reference here. I think maybe the issue I mentioned in my first
paragraph is causing your confusion.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com