SQL multiple primary keys - localization
- by Max Malmgren
I am trying to implement some localization in my database.
It looks something like this (prefixes only for clarification)
tbl-Categories
ID
Language
Name
tbl-Articles
ID
CategoryID
Now, in my tbl-Categories, I want to have primary keys spanning ID and language, so that every combination of ID and language is unique. In tbl-Articles I would like a foreign key to reference ID in categories, but not Language, since I do not want to bind an article to a certain language, only category.
Of course, I cannot add a foreign key to part of the primary key. I also cannot have the primary key only on the ID of categories, since then there can only be one language. Having no primary keys disables foreign keys altogether, and that is also not a great solution.
Do you have any ideas how I can solve this in an elegant fashion?
Thanks.
