Matt Parker writes (in response to Chris Green):
I don't know about mySQL (not a fan really) but in most "commercial" RDBMSs you would actually set multiple primary keys to achieve this, rather than multiple unique constraints.
You can't have multiple primary keys in mySQL, to my knowledge, but you can have multiple unique keys which amount to much the same thing. Those can certainly be combinations of fields.
The main use for the primary key in most software is so that when you edit (or delete) a record in a database the software can generate a suitable query - determine the primary key then "UPDATE ... WHERE <primary key field>=<primary key value for this record>". Having (and knowing) the primary key makes this much easier in software. If the primary key spans multiple fields you're left with ".. WHERE x=X AND y=Y AND ...", which is not only more cumbersome but also much harder to automatically generate. (Think about how you'd write an application like phpMyAdmin without this functionality.)
Within the database schema the primary key is also usually the field used to define relationships etc (but of-course any field will do for that, indexed or not).
I think most "real" databases probably handle this more elegantly, but there you go...