Product table with possible duplicate part numbers from different manufacturers
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.