I suspect if someone can explain to me the best way of handling this example, my confusion about surrogate keys will be over.

I've been trying to avoid asking this question because of the "Read the FAQ" missives, which I've done, but I'm sure I've missed the clue somewhere. In any case, this will save me from asking more.

If nothing else, I suspect this situation will make for a *great* example for the docs if it doesn't exist already.

Three Tables

User (PK - Id)
Language (PK - LanguageCode)
UserData (PK- User.UserId, Language.LanguageCode)

I'd love to make a surrogate PK for UserData, but I don't see how to get the database to enforce integrity for me.

Say I declare a Unique Id field and make UserId and Language Code both many-to-one associations with the respective tables?

What stops me from accidentally creating two UserData records for the same user and Language? Even if I make those two fields FK'd, the database wont stop me from duplicating combinations if their both valid.

I'm sure I'm doing this wrong, but how???

Define a unique constraint with the "natural key"

Doh!

Thank you. I knew it was going to be obvious.