Adding an auto column to an existing database
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? Access can manage without a unique key so why can't any of the mysql front ends do without it? 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. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
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.
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? (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 | +--------+--------+ you just can't tell the difference between to two records. Richard -- Richard Lewis richardlewis@fastmail.co.uk
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. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
Chris Green writes:
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.
Ultimately, for Access to delete a single row it needs to know which row it is deleting. I doubt very much that it is looking at the contents, its simply deleting record number X, where X is effectively a primary auto-incrementing key (whether it is shown to you or not). If you are using Access's forms then it can do all that in the background and never show you, because the information will be held within the form components even if its hidden. In the case of PHP, say, there's nothing hidden; you simply do it yourself. I think other databases tend to have unique record identifiers built in, as opposed to mySQL's requirement that you create one if you need one, and they might not call it a "primary key", but the concept is the same. NB: It's quite typical when defining web forms for manipulating the data to hide the primary key from the user in the same way as Access is doing for you. It's just that you don't have the wysiwyg form designer to do the work for you. -- Mark Rogers, More Solutions Ltd :: Tel: 0845 45 89 555
On Wed, Nov 10, 2004 at 06:41:37PM -0000, Mark Rogers wrote:
Chris Green writes:
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.
Ultimately, for Access to delete a single row it needs to know which row it is deleting. I doubt very much that it is looking at the contents, its simply deleting record number X, where X is effectively a primary auto-incrementing key (whether it is shown to you or not).
Yes, you're probably right. In 'oracle speak' it's using something like a 'row id' (I think that's what it's called).
If you are using Access's forms then it can do all that in the background and never show you, because the information will be held within the form components even if its hidden. In the case of PHP, say, there's nothing hidden; you simply do it yourself.
Chris writes:
The front ends for mysql must have something similar so that when you are, for example, scrolling down a list of records there is something which tells you which record you're at.
Yes: the primary key, if there is one. If you try stuff like this with phpMyAdmin it does its best to work out a unique field (or I think it can cope with a set of unique fields), but if it can't uniquely identify the record then I'm pretty sure stuff like record edit/delete icons will vanish from the view. There is no concept of "the Nth record" in mySQL, or at least if there is it's a recent addition - I've not really played with 4.x past its inherited 3.x capabilities. -- Mark Rogers, More Solutions Ltd :: Tel: 0845 45 89 555
On Thu, Nov 11, 2004 at 11:52:50AM -0000, Mark Rogers wrote:
Chris writes:
The front ends for mysql must have something similar so that when you are, for example, scrolling down a list of records there is something which tells you which record you're at.
Yes: the primary key, if there is one. If you try stuff like this with phpMyAdmin it does its best to work out a unique field (or I think it can cope with a set of unique fields), but if it can't uniquely identify the record then I'm pretty sure stuff like record edit/delete icons will vanish from the view.
There is no concept of "the Nth record" in mySQL, or at least if there is it's a recent addition - I've not really played with 4.x past its inherited 3.x capabilities.
Looking at the Rekall appendices (I think it's there) it tells you how Rekall manages this with various database backends. Postgresql has a rowId type thing (not called that) rather like Oracle whereas mysql doesn't. Thus with mysql a unique column of some sort is required whereas it's 'built in' as it were with postgresql. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
On Thu, 11 Nov 2004 11:53:15 +0000, Chris Green <chris@areti.co.uk> wrote:
Looking at the Rekall appendices (I think it's there) it tells you how Rekall manages this with various database backends. Postgresql has a rowId type thing (not called that) rather like Oracle whereas mysql doesn't. Thus with mysql a unique column of some sort is required whereas it's 'built in' as it were with postgresql.
In postgresql I make a unique identifier thusly: CREATE SEQUENCE jenny_serial START 1; Then apply it to the table: CREATE TABLE t_example ( example_id INTEGER NOT NULL DEFAULT NEXTVAL('jenny_serial'), ...etc This will automatically add an incrementing integer each time you add a record to the table, and can thus be used to link to other tables that want to know about that record. i.e. CREATE TABLE t_postcode ( postcode_id INTEGER NOT NULL DEFAULT NEXTVAL('postcode_serial'), example_id INTEGER REFERENCES t_example, postcode VARCHAR ...etc As far as I can recall, msaccess "auto" numbers don't export to postgres. However, I know nothing about mySQL and the above may not be at all relevant. If mySQL has an interactive sql prompt like psql it is well worth having a look at what the tables really look like without the clutter of a GUI in the way. jen
On 2004-11-11 11:53:15 +0000 Chris Green <chris@areti.co.uk> wrote:
Looking at the Rekall appendices (I think it's there) it tells you how Rekall manages this with various database backends. Postgresql has a rowId type thing (not called that) [...]
Are you sure this is part of PostgreSQL rather than Rekall's backend driver? I didn't find it in the PostgreSQL documentation (rather the opposite) and didn't find the right appendix. -- 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/
On Thu, Nov 11, 2004 at 01:11:45PM +0000, MJ Ray wrote:
On 2004-11-11 11:53:15 +0000 Chris Green <chris@areti.co.uk> wrote:
Looking at the Rekall appendices (I think it's there) it tells you how Rekall manages this with various database backends. Postgresql has a rowId type thing (not called that) [...]
Are you sure this is part of PostgreSQL rather than Rekall's backend driver? I didn't find it in the PostgreSQL documentation (rather the opposite) and didn't find the right appendix.
It'll have to wait until I get home as Rekall is on my Linux box there. I will take a look though as I'm pretty certain I read it right. If it was part of the Rekall driver it could surely do it for other database engines. -- Chris Green (chris@areti.co.uk) "Never ascribe to malice, that which can be explained by incompetence."
participants (5)
-
Chris Green -
Jenny Hopkins -
Mark Rogers -
MJ Ray -
Richard Lewis