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
  • 421 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by chuckc

Dani AI

Generated

Two practical patterns work cleanly in Access; choose based on how the data will be used.

A robust, future‑friendly approach is to give the line‑item table a single surrogate primary key (an AutoNumber) and then enforce the business rule (one line number per order) with a unique composite index on (OrderID, LineNumber). That keeps joins and foreign keys simple (one small integer), isolates the primary key from business‑rule changes, and still guarantees the per‑order uniqueness you want. It also makes debugging, logging and referencing individual rows easier than relying on a multi‑column natural key. This complements points already raised by and the testing done by .

A composite (natural) primary key of (OrderID, LineNumber) is reasonable when the line identifier truly is the canonical, immutable identity and you will not need to reference line items from other tables. Downsides are larger foreign keys (two columns everywhere), more verbose SQL, and fragility if business rules change (for example, renumbering lines).

Access implementation notes and cautions:

  • Enforce (OrderID, LineNumber) with a unique index rather than relying on application logic alone.
  • Avoid client-side tricks like DMax to generate next LineNumber in a multiuser environment unless you wrap the insert in a proper transaction, because race conditions can create duplicates.
  • Use Relationships to enforce referential integrity and consider Cascade Delete only if deleting an order should always remove its lines.
  • For query performance, index OrderID first in the composite index since most access patterns will filter by OrderID.

Example (Access/Jet SQL style):

CREATE TABLE LineItems (
  LineItemID COUNTER PRIMARY KEY,
  OrderID LONG NOT NULL,
  LineNumber INTEGER NOT NULL,
  ItemDesc TEXT(255),
  Qty DOUBLE,
  UnitPrice CURRENCY
);

CREATE UNIQUE INDEX UX_LineItems_Order_Line ON LineItems (OrderID, LineNumber);

Choose surrogate + unique index for most applications; use a composite PK only when the natural key is stable and will never be used as a foreign key.

Recommended Answers

All 3 Replies

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.