RE: How to specify autoincrement primary key value
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]
How to specify autoincrement primary key value
Hello, 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? Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to specify autoincrement primary key value
Denis Gerasimov [EMAIL PROTECTED] wrote on 10/02/2005 10:59:11: Hello, 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. 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. The function of autoincrement keys is to assign unique record identifiers. Do not mix this with other tasks. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to specify autoincrement primary key value
Hello, 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. The function of autoincrement keys is to assign unique record identifiers. Do not mix this with other tasks. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
