On Tue, 9 Nov 2004 13:04:49 +0000, "Chris Green" chris@areti.co.uk said:

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.

You can do this: CREATE TABLE transactions (date DATE, description TEXT, ...) PRIMARY KEY(date, description)

but this will still be true: SELECT date, description FROM transactions WHERE (date=...) AND (description=...)

and as for updates, well you handle them using your wrapper code: e.g. in PHP

$result = mysql_query("SELECT trans_id FROM transactions WHERE date=" . $_GET['date'] . " AND description LIKE " . $_GET['description'] . " LIMIT 1"); if mysql_num_rows($result) = 0 { mysql_query("INSERT transactions (date, description) VALUES ("" . $_GET['date'] . "", "" . . $_GET['description'] . """); } else { echo "<p class="error">Transaction not unique!</p>\n"; }

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.

I think the point of having a field like: CREATE TABLE table (id INT NOT NULL PRIMARY KEY auto_increment, ...)

is for when the other fields of your table are not necessarily going to be unique. Like an address book: none of the fields name, address, postcode, telephone etc. *have* to be unique, so an automatic unique, key column is useful, even though the value in it is unrelated to the data itself.

It allows you to say things like: SELECT date, subject FROM messages, address_book WHERE (name LIKE "%smith%") AND (messages.from_id=address_book.record_id)

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.

Yes, if you can guarantee uniqueness in some meaningful field then you don't use an auto_increment field as primary key.

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?

Richard