Structure of table keys from multiple other column values

I am creating a new database and am thinking about the structure. Earlier I have always used auto-incremented undefined small-INT. There will be plenty of searched performed on some of the tables and the key structured a开发者_JS百科ccording to the above would seldom be known before and the search would therefore be on a non-key column.

In some tables there are other unique values and there we got no problem just putting that as key, but in some there aren't, thus I am thinking that I instead could construct the key as a put-together of two or more column values in order to create a unique string that I can later search for and ensure better performance.

I haven't heard about this key-construction before so I would like to get some input about if I am thinking correctly here. Thanks!

Here is an example that will illustrate this "put-together"-key:

mysql> CREATE TABLE example (
     key VARCHAR(100),
     name VARCHAR(50),
     category VARCHAR(20),
     country VARCHAR(30)
   );

name is not unique per se but is unique per category and country (which is ensured at input). The searches will 90% of the time involve these three parameters and thus the code doing the search can put together the key and search for the table id/key. In the 10% of cases when one of the parameters are unknown the search can be made for other columns example on countrydirectly if the user wants to see all rows with country=xyz.

---

Yes it's completely legal to use two or more columns as a unique identifier, it's called composite key.

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx