If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do you create a composite key that can be referenced?
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. -- bws93222 |
#2
|
|||
|
|||
How do you create a composite key that can be referenced?
hi,
bws93222 wrote: 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 -- |
#3
|
|||
|
|||
How do you create a composite key that can be referenced?
On Sat, 18 Apr 2009 09:48:04 -0700, bws93222
wrote: 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 |
#4
|
|||
|
|||
How do you create a composite key that can be referenced?
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 "Armen Stein" wrote: On Sat, 18 Apr 2009 09:48:04 -0700, bws93222 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|