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.
The oft recommended 'Auto' column seems a bad way to do things because it doesn't prevent duplicate entries, you could inadvertently put 'Fred Bloggs' into your address book lots of times, each with a different value in the auto 'ID' column. It really seems to me that the auto column approach is a quick and dirty bodge which loses some (most?) of the advantages of a unique key and wastes space as well.
Joins between tables can just as easily be done on the 'real' values as on an articifial unique key. My transaction types are held in a separate table and the join to the transactions table (when needed) is simply done with the transaction type code. This again guarantess what one wants which is that one can't enter a non-existent transaction type in the transactions table. I can see no advantage at all in having an additional, numeric, ID column in the transaction types table.
Have I missed something here or is it just that the documentation I'm looking at is trying to make things 'simple' and using an auto column is the 'simple' way to do it?