First of all, thanks for replying. but, u didnt get my point. I want to use numeric field only as the key field, but not the normal auto increment..
----- Original Message ----- From: "Daniel Kasak" <[EMAIL PROTECTED]> To: "Nitin" <[EMAIL PROTECTED]>; "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, June 12, 2004 3:53 PM Subject: Re: autoincrement problem > Nitin wrote: > > >Hi all, > > > >What will be the best way to implement auto increment field. I dont want to use auto increment feature, as it only appends the numbers but doesn't check for the values deleted. Suppose, following are the values in table > > > >1 abc > >2 bcd > >3 cde > >. . > >. . > >. . > >9 xyz > > > >now if 2nd and 3rd rows are deleted, an autoincrement field will still assign 10, 11, 12.... to the new values, while I want it to assign the values deleted from the table first. > > > >What will be the best way to implement it? > > > >Thanks in advance > >Nitin > > > > > You shouldn't use text fields as primary keys. It's much faster and more > efficient to use a numeric field. > You're also asking for trouble trying to re-use keys. What happens if > someone has a key 'bcd' and then their record gets deleted, and someone > else gets the key 'bcd'. You go back over data and see reference to > 'bcd', and have no idea what it's referring to unless you get the > transaction logs out and check what data the key was referring to on > that particular day. It will be an absolute nightmare to debug, and you > destroy any sane way of auditing your data. > The best way to implement it, therefore, is to *not* implement it. Use > MySQL's auto_increment field as the primary key. If you absolutely > *must* reuse the text 'keys' you have above, then that's up to your > application to handle. Make a char / varchar field and have your code > scan the table for the next available key and then use that in your > 'insert' statement. But it's a *really* bad idea. > > Dan > > -- > 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]
