CODEWITHSUNDEEP
ms-accessprimary-keyms-access-2003composite-primary-key
user3805867
user3805867

Reputation: 5

Composite primary key in Access

I have a dataset for which the sole means of identifying each record is a customer name and a certificate number. For each purchase, the customer has a different certificate number (IE CustA has certificates 1-4, while CustB has certs 1-650) Neither field is unique, however together they uniquely identify a record. When I try to create a composite key, Access tells me that they would create duplicate values in the primary key. These keys are system generated so there cannot be a mistake with the naming/numbering.

Any way to fix this? My understanding is that this is how the system should work and that my dataset is errant, though I am assured that is not the case.

Upvotes: 0

Views: 744

Answers (1)

iDevlop
iDevlop

Reputation: 25252

This is no Access issue, this a data issue. A PK cannot - by definition - have duplicates or Nulls. Make a query to identify the culprits in your data: 

select CustName, CertNum, count(*) as Counter
group by CustName, CertNum
having count(*) > 1

Otherwise your can create a surrogate PK using a Counter.
If it is a matter of Nulls, like cjb110 suggested, I can create a Unique Index with IGNORE NULLS. But until you fix nulls and duplicates, your 2 columns won't fit as a natural PK.

Upvotes: 2

Related Questions