On 9/11/2004, "Chris Green" chris@areti.co.uk wrote:
Lots of the GUI front ends for mysql that I have been looking at are very insistent that the tables one uses have a unique primary key to guarantee uniqueness.
I don't quite understand why this is so necessary but this isn't particularly important at the moment as the database tables I've imported have unique rows.
However neither mySql nor the front-ends seem to know about unique keys made up of more than one column, is this because such a thing is regarded as a rather advanced approach or because it's actually not possible in mySql?
My unique key is the combination of date (the date of the transaction) and the transaction description. This means that there can be the same transaction on different dates, e.g. if I buy some stamps on two different dates the description can be 'Stamps' for both of them. The advantage is that I am protected against entering the same transaction twice by mistake as this will transgress the unique key requirement. If I really *did* buy stamps twice on the same day then I just put 'Stamps again' or some such.
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.
Matt