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:[EMAIL PROTECTED] > Sent: Thursday, February 10, 2005 2:31 PM > To: Denis Gerasimov; [EMAIL PROTECTED]; '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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
