Relational Databases allow you to use Compound Primary Keys. But should you?
Both Access and SQL Server allow you to create Compound Primary Keys. They may seem like a good idea for some tables. But don’t… It’s a trap.
For example, you may be thinking of using a compound PK in a joining table like Customer Contact where the PK is made of of Customer ID and Contact ID so you can’t create duplicates. Don’t. Use an Autonumber PK and instead create a composite unique index on those two fields.
You may think that your joining table will never need a child table to relate to it but compound PKs will always come back to bite. Imagine in the above example that you now need a table for contact details for your Customer Contact table. A table that has individual records for the contact for things like email, mobile, landline… Much easier to join on the Customer Contact ID column than on Customer ID + Contact ID.
Access will allow compound PKs but won’t allow you to use them in relationships (otherwise know as Foreign Keys or FKs). You can still join on multiple fields in queries but that is best avoided.
The Access Relationship Diagram tool won’t allow you to create a FK on multiple columns. The Parent field must have a unique index. ie a single column PK.
In SQL Server, you can create FKs that include multiple columns, just like indexes do. But that doesn’t mean you should. Using Compound PKs means that your queries need to join on multiple columns and that all gets too hard.
I’ll go into FKs in detail shortly but for now… Keep it simple, don’t use a compound PK, and your FK only needs to reference one column in the Child table and one (the PK) in the Parent table.