Primary keys in mysql databases
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? -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
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
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... -- Mark Rogers, More Solutions Ltd :: Tel: 0845 45 89 555
On Tue, Nov 09, 2004 at 01:37:44PM +0000, Matt Parker wrote:
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.
Yes, I'm familiar with Oracle SQL at work. It may just be my quick skim of the mySql (and other) documentation hasn't made clear (to me!) that they're saying the same as you. However, does having multiple primary keys mean that the combination of the primary key columns is guaranteed to be unique? Ah, yes it does, I've just looked it up in my Oracle book on my desk here. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
In fact mysql does allow multi-column primary keys, I just searched the documentation on the mysql site:- A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so will mark only that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
On 2004-11-09 13:37:44 +0000 Matt Parker <matt@mpcontracting.co.uk> wrote:
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.
MySQL is commercial, produced by MySQL AB. MySQL is a privately-held Swedish company which offers support, alternative licensing and franchising services. It's just a bit lightweight and I'm not a big fan of it. It looks like multi-column primary keys are supported these days, from http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html -- MJR/slef My Opinion Only and not of any group I know Creative copyleft computing - http://www.ttllp.co.uk/ Unsolicited attachments to the pipex address deleted Will HLF fund tree-killings? http://www.thewalks.co.uk/
participants (4)
-
Chris Green -
Mark Rogers -
Matt Parker -
MJ Ray