Re: Update table to get consecutive Primary keys

On Thursday 08 June 2006 23:58, Antonio Bassinger wrote: > Hi All! > > I've a MySQL table: > > table (id INT NOT NULL AUTO_INCREMENT, > flag TINYINT NOT NULL, > msgID VARCHAR(30) NOT NULL, > msgName VARCHAR(30), > size INT NOT NULL, > PRIMARY KEY(id)); > > > If I delete a row, say 5th, the id entries are 1 2 3 4 6 7 ... 10. > > I wish to keep entries consecutive, that is rearrange the table as 1 2 3 4 > 5 6 ... 9. How do I go about it? > > Thanks & Regards, > Antonio Simple. Don't. The whole point of having a numeric primary key is that it is both unique and constant. If you change the primary key, then you have to change every reference to it in every other table as well. You've just defeated the purpose of having a non-user-viewable primary key in the first place. You may as well just use the msgName field, save yourself a column in the database, and still have a huge problem with data integrity. As I said, don't do it. Stick with the numeric ID and don't change it. You gain nothing by re-naming your entire database. -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson