On Wed, Nov 10, 2004 at 04:22:01PM +0000, Richard Lewis wrote:
On Wed, 10 Nov 2004 16:00:30 +0000, "Chris Green" chris@areti.co.uk said:
If I add an 'auto' column to an existing database to provide a unique key (I don't believe this should really be necessary, see below) will it get automatically populated with a sequence of integers as required?
Yes.
OK, thanks.
Access can manage without a unique key so why can't any of the mysql front ends do without it?
Last time I looked at MS Access (v. 97?) you had to have a primary key in your tables to do all sorts of things and it used to warn you that you needed one and (I think) created one automatically...
I had a look at my Access database and, although the rows as a whole are unique it turns out that I *don't* have a multi-column primary key to give a unique key. However the database copes quite happily with multiple purchases of stamps with identical descriptions and with pension payments of the same amount every month. It certainly works and I can delete one pension payment without affecting the others for example.
I'm not quite sure what you mean?
In my forms (for example) I can scroll through the transactions and delete one of the transactions which would appear to have a lot in common with one of the other ones and Access really does only delete the one row.
I did think I had a primary key using multiple columns but it must have got 'lost in the wash' while doing other changes to the database. There certainly isn't any sort of unique key that Access (and it is Access 97) shows if you look at the table design details.
(Did Access used to have a option to hide columns in tables [for viewing]; maybe its hiding an automatic primary key field?)
There is nothing wrong with having a table like this:
+--------+--------+ | fname | sname | +--------+--------+ | John | Smith | | John | Smith | +--------+--------+
What I have is something like:-
Date Description Amount 10/11/04 Stamps 3.49 10/11/04 Stamps 4.72
... and other similar things. As I said I can delete (say) the 3.49 transaction using my Access forms and it does just delete that transaction and not the 4.72 one as well even though there is no key which will differentiate the two rows.
Maybe it's down to the closer relationship between Access and the Jet database engine. Maybe also if I used Access via ODBC to a remote detabase things would be different.