Can you use two fields to make the primary key in Access?

[msmithtlh]

I would like to use 2 fields together for the primary key. Is this possible in Access?



Example where congregation ID plus the team ID making up the primary key.

Congregation 123 has teams 1, 2, and 3. Congregation 234 has teams 1 and 2.
primary keys from first would be 1231, 1232, 1233.
primary keys from second would be 2341, 2342.

Is this possible? If yes, how do you create it?

[June7]

I advise to avoid compound keys, only use as last resort.

If this compound key is not actually saved to a dependent table then it really is not necessary to define as a compound primary key. What you can do is set a compound index to prevent duplicate pairs.

[Micron]

I advise to avoid compound keys, only use as last resort.

Yet this is exactly what is being done here under the topic "many to many relationships"?

https://learn.microsoft.com/en-us/of...-relationships

[June7]

I know, and the intent is probably just to prevent duplicate pairs which can be accomplished with compound index. I doubt the compound key is actually saved to a dependent table, at least not that example.

[Micron]

All in all, I've spent a lot of time researching this notion and today I think I arrived at a destination.

There's a lot out there about compound pk's (cpk) - much of it supported and touted for things like SqlServer. There's very little dissenting information - it's all regurgitation of "don't" but those who say don't never say why. The one exception I found is a post by Pat Hartman that made sense for about 5 minutes - that you'd need a single pk in order to use a list or combo, so use a compound index and a pk field instead. After 5 minutes I thought why on earth would you create a listbox that showed unique combinations of foreign keys, all on the many side as exists in a junction table? I cannot imagine anyone needing to pick from a list that showed
A . . B
A . . C
A . . D
A . . A
B . . B
and so on, especially since it is not possible that the related parent keys don't exist in other tables. To sort/query/whatever from the back end of a relationship doesn't make sense to me. The usual approach is seek the parent first then the related child records. What I did find on the other side of the debate is stuff about clusters and how cpk fields are indexed individually whereas in a compound index every row in every field in the index has to be searched. The distinction has been likened to a phone book: you normally search by last name first, then by first names with those last names. With a compound index, it seems you're basically searching for all "David" first, then finding the last names from that list that you were looking for. With the compound index, field order matters so you can make it worse with the wrong order. With the index, you must have "Required" set to yes for every field in the index, otherwise you can get duplicate combinations where there are more than 2 fields. "So? I've never had bad performance with a compound index" you might say. Doing things inefficiently, no matter what that is, seems to be frowned upon regardless of the fact that in many cases the efficiency is barely measurable given the high powered processors in use. Yet here we are, willing to create compound indexes because so many say "don't".

With a compound key, you cannot have dupes or nulls in any field and as I said, each field is indexed instead of having a single index comprised of 2 or more fields.

So I'll be happy to create compound keys until I either give up databases or anyone points me to information that says I should do otherwise and backs that up with reasoning that makes sense, or if I ever discover that I misinterpreted anything in my research.