List:General Discussion« Previous MessageNext Message »
From:Denis Gerasimov Date:February 18 2005 3:20pm
Subject:RE: How to specify autoincrement primary key value
View as plain text  
Hello,

Sorry for my insistency but I would like to resume this discussion again.

A few days ago Gleb answered the question I asked before about 0 PK values
(look for "Re: auto_increment=0" topic in the archives).

I had a look at http://dev.mysql.com/doc/mysql/en/server-sql-mode.html and
found that using 0 is not recommended for PK values. But could you explain
WHY?

As I mentioned before I used 0's for special purpose, e. g. for specifying
root user/category. What is the recommended way of doing this?

How do you specify, for instance, root category for products (if we want to
have _one and only_ root category)?

> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@stripped]
> Sent: Thursday, February 10, 2005 2:31 PM
> To: Denis Gerasimov; Alec.Cawley@stripped; 'MySQL General List'
> Subject: Re: How to specify autoincrement primary key value
> 
> 
> > > > One simple question... AFAIK I can specify value for an
> autoincrement
> > > > primary key (int) when inserting a record like this:
> > > >
> > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test')
> > > >
> > > > But it doesn't work for id = 0. Why?
> > > >
> > > > I would like to use some primary key values for special purpose,
> e.g.
> id
> > > 0
> > > > means root/default record and so on.
> > > > Is that recommended? Are there any alternative ways?
> > >
> > > Autoincrement starts from 1. That is the way it is, and cannot, I
> think,
> > > be changed.
> >
> > Yes, it starts from 1, but concrete value CAN be changed to 0 manually.
> >
> > Maybe it is better to ask: "Why can I set autoincrement primary key to 0
> but
> > cannot do this using INSERT statement?"
> >
> > >
> > > I would advise against using autoincrement keys for "special" uses.
> This
> > > is muddling two different functions into one. Remeber that primary
> keys
> > > must be unique: if, at some time, you need to have more than one of a
> > > "special" value, you will be in trouble.
> >
> > Sure, but the idea was to use 0 for root category or "root" access. It
> seems
> > to be reasonable at least.
> 
> Then again... this is only an non-business-meaning ID value. If it's 0
> or 100 or 1293492342 -> it doesn't matter at all.
> 
> > > The function of autoincrement
> > > keys is to assign unique record identifiers. Do not mix this with
> other
> > > tasks.
> 
> The meaning of auto-inc values is to make people lazy.
> 
> With regards,
> 
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1

Thread
How to specify autoincrement primary key valueDenis Gerasimov10 Feb
  • Re: How to specify autoincrement primary key valueAlec.Cawley10 Feb
RE: How to specify autoincrement primary key valueDenis Gerasimov10 Feb
  • Re: How to specify autoincrement primary key valueMartijn Tonies10 Feb
    • RE: How to specify autoincrement primary key valueDenis Gerasimov18 Feb