Can I renumber my primary index?

[BinkyM]

Hi, folks:

I'm working on someone's database where the records are numbered sequentially, but a LOT of records have been deleted. The current high record number is over 7000, but there are only 2000 records in the database. This might not seem like a problem, but I've put an HTML interface on it so the user can look at a hundred records at a time using a pop-up menu offering 1-100, 101-200, 201-300, and son on. If he'd like to look at record 4278, he has to guess where it is, because it might be in 801-900, or 1201-1300, etc. What I'd like to do is to renumber all the records so they're sequential, without the big gaps. Can I do this, and if so, how? I've not been able to locate a page which describes it, so I'm kinda thinking he's stuck out in the cold.

Thanks for any tips!

[GJay]

if nothing references the values, then you can drop and re-add the column.

[oracleguy]

If he knows what record he is looking for, why not allow him to just type the record number in and retrieve that record? Versus having to page through looking for it.

Renumbering primary keys isn't something you normally want to do on a relational database since it would be break everything.

[BinkyM]

GJay:

drop and re-add the column

Duh! That's too easy! I was looking for a line of SQL to do it. Thanks! That's great!

oracleguy:

If he knows what record he is looking for, why not allow him to just type the record number in and retrieve that record?

'Cause most of the time, he only knows a range, like the 5200s, and needs to eyeball 'em to know what it is he's looking for.

Thanks, folks!

[guelphdad]

A primary key is used to identify your data as unique from one another.

sometimes you can use something like a social security number if you know the data isn't going to repeat.

Sometimes you can use a login name combined with a first and last name for instance as your primary key.

then there are other times when it just makes sense to create an auto_increment column to identify them as the primary key because your data has no other candidate that would be obvious for a primary key.

what you should not do though, is rely on your primary key as an item you would search on in your database. it is a bad practice.

you also can't use more than a single table. That is, you are defeating the purpose of a relational database since you can't have a second table relating to data in the first table.

If you do then you should be using your primary key in your first table as a foreign key in your second table to relate the two tables.

Thus in your case if you renumber your primary key in your first table, you are corrupting the values in your second table because there is no longer a foreign key relationship.

Don't worry you are not alone, on almost every single discussion board I've been on, there is a question every two weeks, if not every week, about renumbering a primary key to eliminate gaps.

In short don't do it. Identify your data in a different way, don't rely on the primary key to follow some arbitrary sequence of data.

[BinkyM]

guelphdad:

there is a question every two weeks, if not every week, about renumbering a primary key to eliminate gaps

That's pretty funny; I thought it was just me!

This database was created by a boobyhead, and I'm stuck with it now. The auto-incrementing primary key does nothing but make the record unique. A second field is what ties this database to a second.

[guelphdad]

Okay if you have a second column relating the data to your other table then why are you not having your users search on that?

[oracleguy]

Don't worry you are not alone, on almost every single discussion board I've been on, there is a question every two weeks, if not every week, about renumbering a primary key to eliminate gaps.

In short don't do it. Identify your data in a different way, don't rely on the primary key to follow some arbitrary sequence of data.

Yeah, here is a similar thread: http://www.codingforums.com/showthread.php?t=67944