To the originator of this thread:
While what you are asking is possible, Id like to put my two cents in and
ask you please not to do it.
If this is a production database, you have no idea how helpful it will be
for you to know by those missing pk numbers that something was deleted
manually from the database as opposed to an index error, or some other more
disasterous problem. In my experiece leave those holes in the pk sequence,
and then later on when someone complains that a record is missing, you can
say with some confidence that someone actually deleted it.
AS a followup note, I would never allow user permissions to let a regular
user delete a record in the first place, and I would stop it with either
code, or database user access privelages. Instead create an active/inactive
status field and then you can rewrite some code to add a "where status =
'active'" or similar line of code
Then create a "purge records' function in your app so that admins who are
supposed to know what they are doing can delete records if they really want
to.
Just my opinion.
Rance
PS. hey filter, chew on this, MySQL, SQL,
>From: "Arthur Fuller" <[EMAIL PROTECTED]>
>To: "Harrison C. Fisk" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, "Paul
>DuBois" <[EMAIL PROTECTED]>
>Subject: Re: Get the next AUTO INCREMENT value for a column
>Date: Sun, 5 May 2002 14:06:43 -0400
>
>IMO the desire to know this answer suggests that something is wrong with
>the
>design. Again IMO, a PK must be absolutely meaningless. Any attempt to
>impost meaning on it is by definition wrong.
>
>By my lights, the only PK you want to know is the one just added. That's
>enough information to let you start adding rows to child tables. And that
>value you can easily obtain.
>
>Arthur
>
>----- Original Message -----
>From: "Paul DuBois" <[EMAIL PROTECTED]>
>To: "Harrison C. Fisk" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
>Sent: Saturday, May 04, 2002 12:34 PM
>Subject: Re: Get the next AUTO INCREMENT value for a column
>
>
> > At 12:29 -0400 5/4/02, Harrison C. Fisk wrote:
> > >Actually I think the SHOW TABLE STATUS like 'TABLENAME' command can
>show
> > >you the next auto_increment value.
> >
> > There is no guarantee that this value will actually be used for the
> > next AUTO_INCREMENT value. Suppose another client issues the following
> > query:
> >
> > ALTER TABLE tbl_name AUTO_INCREMENT = 123456;
> >
> > If you were relying on what SHOW TABLE STATUS said, you'd be hosed. :-)
> >
> >
> > >Although I can say I wouldn't really ever use it except for personal
>use,
> > >not in an actual application.
> > >
> > >Harrison
> > >----- Original Message -----
> > >From: "Paul DuBois" <[EMAIL PROTECTED]>
> > >To: "Son Nguyen" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > >Sent: Saturday, May 04, 2002 12:16 PM
> > >Subject: Re: Get the next AUTO INCREMENT value for a column
> > >
> > >
> > >> At 9:01 -0700 5/4/02, Son Nguyen wrote:
> > >> > CREATE table test1 (ID int NOT NULL AUTO INCREMENT, name text,
>value
> > >> >text, Primary Key (ID));
> > >> >
> > >> > INSERT into test1 (name, value) values ("test1", "value1");
> > >> >
> > >> > INSERT into test1 (name, value) values ("test2", "value2");
> > >> >
> > >> > DELETE from test1 where ID=2;
> > >> >
> > >> > How can obtain the CURRENT value of next ID AUTO INCREMENT value
> > >> >(which is 3 right now)
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > Son Nguyen
> > >> >mysql, query
> > >>
> > >> You can't. AUTO_INCREMENT values are not available until you
>generate
> > > > them.
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
