Member Avatar for chuckc

In defining MS Access tables where a 1 to many relation exists what is the best way to define a table containing the “many” records. For example, a database with orders and line-items might have the “line item” table with an order-number and a line-number which would define a unique record. Is there a way to create a compound primary key for this with Access 2000?

Since duplicates are allowed, the order number itself can not be a primary key of the line item table, so is it necessary to have a “primary key” or would it be reasonable for such a table not to have a primary key?

Or is it better to simply add an auto-number field to the table?

  • 2 Contributors
  • 3 Replies
  • 389 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by chuckc
Member Avatar for timothybard

I would always recommend having a primary key; if you don't need it now, perhaps you will need it later; if nothing else, it will help you refer to records while debugging.

MS Access does support compound primary keys; simply open the table in design view, select the fields to be used as the compound key and click the key icon.

In general, I have no opinion on the use of a compound key vs a non-compound key; however, in your situation, it sounds like you should not use a compound key. Can more than one record have the same line-number? If not, then the line-number can be used as the primary key since it uniquely identifies the record; the order-number can simply be a foreign key. A situation where a compound primary key would be appropriate is when two fields do not uniquely identify a field by themselves, but the combination of the two fields does. A simple example can be the fields 'row' and 'column'. Neither field can identify a record uniquely, but when used together, a record is uniquely identified.

commented: Excellent suggestion on Access DB +1
Member Avatar for chuckc

Good point, about debugging! I had not thought about that.

Re. "Can more than one record have the same line-number? " Yes. In this case there will be many records (or rows) with line number 1, since every order will have at least one line. The combination of order number and line number will always be unique, at least it should be; but in this case I agree that the auto-number field sounds like the best design.

BTW, I tried defining a compound key that way, but it didn't work. As soon as I "right click", one of the two fields deselects. Is that possibly a new feature, or did I miss something in the instructions?

Thanks and regards...

Member Avatar for chuckc

Re. primary key comment in my previous reply, I was able to create a compound primary key as you suggested. I tried it in a test database and at least for the application I described, i.e. where these two fields uniquely identify a particular record, it works well and I think a compound primary key is the best solution.
Thanks again!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.