1

I am making a database. It has a product(aka component or part) table with a ID, ManufacturerID, and PartNumber. The ID is obviously the primary key and is unique. The ManufacturerID is pulled from the ManufacturerT. The PartNumber will be entered by the user. I am trying to decide whether to make the PartNumber field unique or not. If I do, I won't have the issue with a user entering the same product multiple times, but what if there is a product from a different manufacturer with the same PartNumber? It wouldn't allow the user to enter. What is the solution to this? Does it require some special logic? Is there a way to not allow duplicates if the ManufacturerID and PartNumber fields are identical? I am using Microsoft Access 2013(version 15).

Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
aknewhope
  • 11
  • 3

1 Answers1

1

A common solution to this problem is composite keys or indices. You can specify any key or index as unique, so combining the PartNumber and ManufacturerID into a composite and then marking it as unique has the effect of enforcing the uniqueness of each combination of PartNumber and ManufacturerID. See this thread on SO for more information.

Community
  • 1
dartonw
  • 1,357
  • 9
  • 11