Resetting the primary key

[Ripley]

Hi. Is there anyway to re-order the primary key without having to extract all the records to excel and importing them back again?

Ie. When deleting some records and creating new ones, it skips the next consecutive number becuase there was a record with that key proviously.

 

[KenHigg]

Save your effort and don't try and use the autonumber feature like you've described...

 

[Ripley]

yeah but surely there is a way of re-numbering the primary key?

 

[KenHigg]

Once it's there it's there. Now if you delete some off the bottom, if you compact and repair it should start back at the lastest, largest number. Other than that you'd have to create some custom code to add a new auto key column, delete the old one, etc... Then you'd have to worry about messing up all your related tables... Just a mess...

 

[Ripley]

hmm i think its easier to delete the autonumber then thanks for you fast response

 

[KenHigg]

Just curious - Why do you need the a fld that is capable of being re-numbered?

 

[Ripley]

just simply because it looks un-tidy if the numbers dont follow a consecutive pattern, im a bit of a perfectionist to be honest

 

[KenHigg]

I see - does appear to be a bit unkempt...

 

[Ripley]

lol me not keep my database uptodate? never!

 

[modest]

When you renumber these, you will have to renumber every place they appear in other tables as foreign keys. Your wanting to do this is rediculous, but just create a copy of the table.

Delete the primary key field, then recreate the primary key field, then copy the data in.

 

[KenHigg]

When you renumber these, you will have to renumber every place they appear in other tables as foreign keys. Your wanting to do this is rediculous, but just create a copy of the table.

Delete the primary key field, then recreate the primary key field, then copy the data in.

I don't think I'd go as far as ridiculous. Impractical maybe...