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
|
|||
|
|||
2 tables related by primary key have different records for the rec
I have two tables: tblOrders and tblOrder Detail. The primary key is Order
ID. The record for Order ID #4983 on the tblOrders table is different from Order ID #4983 on tblOrder Details........why???? I have referential integrity and all the boxes checked but the basic reason you have a primary key is so that this doesn't happen right?? So what do I do now? I have no way to control the integrity of the data. |
#2
|
|||
|
|||
2 tables related by primary key have different records for the rec
Are you saying that both tables have a same field (OrderID) as primary key?
If your situation requires a single order detail record per each order, that would make sense. If you require one/more order detail rows per order row, having these two tables related by their primary keys does not. A typical design would have the primary key of the order table (OrderID) be related, one-to-many, to a foreign key field in the order detail table. Note that this means the order detail table needs its own, unique primary key. Can you describe your situation a bit more? Regards Jeff Boyce Microsoft Office/Access MVP "TechyTemp" wrote in message ... I have two tables: tblOrders and tblOrder Detail. The primary key is Order ID. The record for Order ID #4983 on the tblOrders table is different from Order ID #4983 on tblOrder Details........why???? I have referential integrity and all the boxes checked but the basic reason you have a primary key is so that this doesn't happen right?? So what do I do now? I have no way to control the integrity of the data. |
#3
|
|||
|
|||
2 tables related by primary key have different records for the rec
On Tue, 16 Jan 2007 11:55:01 -0800, TechyTemp
wrote: I have two tables: tblOrders and tblOrder Detail. The primary key is Order ID. The record for Order ID #4983 on the tblOrders table is different from Order ID #4983 on tblOrder Details........why???? I have referential integrity and all the boxes checked but the basic reason you have a primary key is so that this doesn't happen right?? So what do I do now? I have no way to control the integrity of the data. As Jeff says, this is quite odd: normally the Order Details table would have MANY records for an order, and by making the primary key the OrderID you're insisting that there be only one. And are you assuming that you can have other fields duplicated between the two tables, storing them redundantly, and somehow the relationship will keep them up to date? That's NOT how relationships work if so! The *only* field that tblOrders and tblOrderDetail (I'd suggest getting rid of the blank in the name) should have in common is the OrderID; that field should be the Primary Key of Orders and a foreign key field (NOT primary) in OrderDetails. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|