main

main@lists.alug.org.uk

10372 discussions

Primary keys in mysql databases
by Chris Green 09 Nov '04

09 Nov '04

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(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

4 5

Re: [ALUG] Primary keys in mysql databases
by Richard Lewis 09 Nov '04

09 Nov '04

On Tue, 9 Nov 2004 13:04:49 +0000, "Chris Green" <chris(a)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 -- Richard Lewis richardlewis(a)fastmail.co.uk

1 0

Re: Firefox editor
by tom potts 09 Nov '04

09 Nov '04

I've just updated to Firefox ve 1 releas and http://cuneaform.mozdev.org/editor/index.htm no longer works with it! ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com

1 0

test
by syd 09 Nov '04

09 Nov '04

apologies

1 0

Skype is pretty good
by Wayne Stallwood 08 Nov '04

08 Nov '04

But my headset is evil, it digs into my ears and the sound quality is terrible. Does anybody know if any of the Plantronics USB headsets can be made to work with Linux. At the moment I have the annoying situation where I have a headset plugged into the front panel of my PC, this mutes the speakers (I could solve that but then if I left the headset plugged in I would get an annoying "tish tish tish" comming from the headphones when the music is turned up) So I have to leave the headset unplugged until a call comes in (or I want to make one) and then fumble around to get the Headphone and Mic socket in (or as with the other day, round the wrong way) So I really like the idea of a USB device that (presumably) acts as another sound device, however the Plantronics headsets are not very cheap and I don't want to waste my money if they don't play on Linux

2 2

Further database progress migrating from Access
by Chris Green 08 Nov '04

08 Nov '04

Well I decided that knoda isn't what I need for forms design etc., it's simply not powerful enough, no calulated fields in forms, no triggers, etc. So I was getting a bit despondent really but decided to look and see if the postgresSql site had any useful links, I;m not stuck on mysql, postgresql is just as possible. Well, after a few blind alleys, I came across Rekall and, so far, it looks as if it's just what I want. It works with mysql as well as with other databases so no problems there. It's a KDE application but I just downloaded the source tarball, did a .configure and make and the thing built flawlessly. It took a *long* time though, it's a big application. It seems to have the forms capability that I want. It has macros (rather like access) but also has more (for me) understandable scripts written in python. I'm just exploring at thehme moment but I think I have what I want. It's remarkably well hidden for such a comprehensive application. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

1 0

Speaking of firefox ....
by Chris Green 08 Nov '04

08 Nov '04

Does anyone else find the 'download manager' in Firefox really annoying? You have to actively clean up the old entries if you want it to stay sensibly small instead of occupying a huge chunk of screen. What use is the download manager anyway? I really can't see any use for a history of past downloads. I'd really much prefer the old download system whereby one got a little window popped up for each download which disappears for ever when completed (with the option of keeping it until you dismiss it yourself). By default it produces little screen clutter but it can cope with multiple, simultaneous downloads quite happily. The new system is just a nuisance. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

5 10

Firefox editor
by tom potts 08 Nov '04

08 Nov '04

Someone couldnt finds the editor: http://cuneaform.mozdev.org/editor/index.htm however http://www.fckeditor.net/ can be used 'over the web' so can be made part of a documentation system.... ___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com

3 2

mysql 'forms' data entry designer
by Chris Green 08 Nov '04

08 Nov '04

My migration of my Access databases to mysql has started. I've got mysql, PHP and apache up and running and I've imported the data from my Access database. I can see it all in phpMyAdmin, most impressive! Now I need to be able to design/build replacements for the Access data input forms and the Access reports. Starting at the input end (seems reasonable!), has anyone got any recommendations for designing input forms for mysql databases? Searching Google isn't very helpful because as soon as you enter 'mysql forms PHP' (with or without the 'PHP' actually) you get zillions of hits on HTML forms for entering data via PHP into a mysql database. The problem is that this gives a rather backwards approach which assumes a 'typical' HTML form and that's not what I want really. For data entry it doesn't really even have to be a web form. What I'm after is essentially a view of my table with some calculations/filters between data entry and the database. I.e. when the 'form' is opened it loads up the whole database table and presents it on screen in tabular fashion. I can edit existing entries simply by moving to them and changing them, I can add entries at the end. Most fields simply get inserted unchanged in the database as I type them but some fields are automatically calculated and inserted when I enter/change other fields in the same row. This is what I've done in Access and I want to do something similar for the mysql database if I can, preferably with some sort of tool to help me! It's the sort of thing Oracle Forms does too. Surely someone must be doing it for mysql. The phpMyAdmin 'Browse' window looks somewhat like what I'm after but I need to be able to edit the data in place (I.e. move to the field and change it), add rows at the end and have 'auto calculated' values. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

2 7

mysql users, I still seem to be a bit confused
by Chris Green 08 Nov '04

08 Nov '04

Well I have just about everything working now. I've managed to install apache2 and PHP5 (I don't quite understand what I did wrong the first time but going back to PHP4 and then to PHP5 again got it working). I've also installed phpMyAdmin and that's working fine too. However I still can't get ordinary users to work the way I expect. I'm using phpMyAdmin to do all this now. I deleted all the entries for user 'chris' and then added a new user 'chris' with host set to 'Any host', with password set to 'No password' and with all privileges set. This appears to work OK, phpMyadmin says I have a user as follows:- chris % No ALL PRIVILEGES Yes Edit (No is 'Password' and Yes is 'Grant') However if I then say 'mysql -uchris' and try 'use fred' I still get:- ERROR 1044: Access denied for user: '@localhost' to database 'fred' If, on the other hand, I do exactly as above except I set host to 'Local' then user chris works as expected. Something is certainly awry somewhere, it may be my understanding but at the very least things are not quite how one would expect from what one is being told. Is there some security thing that refuses to allow an 'Any host' user to have lots of privileges? No, I've tried creating a user with few privileges and 'Any Host' and they get the 'Access denied...' error too. It seems to me that the 'Any Host' doesn't work. -- Chris Green (chris(a)areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."

4 5

Jump to page:

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

2003

2002

2001

2000

1999

1998

1997

main