1

Im programming a program in java and i have a database in a JTable just like the ones below. I wanted to know if it is possible to refresh the primaryID location from 1 on the GUI interface form one when a row is deleted? for example below the LoactionID is deleted for London and added again with an id 4. Is this possible? enter image description here

Im using SQL in java

user1992697
  • 315
  • 1
  • 5
  • 11

1 Answers1

3

To answer your question, yes it is possible.

There is no good reason for you to do this though, and I highly recommend you don't do this.

The only reason to do this would be for cosmetic ones - the database doesn't care if records are sequential, only that they relate to one another consistently. There's no need to "correct" the values for the database's sake.

If you use these Id's for some kind of numbering on the UI (cosmetic reason):

Do not use your identity for this. Separate the visual row number, order or anything else from the internal database key.

If you REALLY want to do this,

Google "reseeding or resetting auto increment primary ID's" for your sql product.

Be aware for some solutions if you reset the identity seed below values that you currently have in the table, that you will violate the indentity column's uniqueness constraint as soon as the values start to overlap

Thanks Andriy for mentioning my blindly pasting a mysql solution :)

Some examples:

Community
  • 1
  • 1
Timmetje
  • 7,641
  • 18
  • 36
  • Agree with your main message about not using an ID column for anything else but identification purposes. However, your suggestion to reset the autoincrement value, and more particularly the ALTER TABLE statement to do the job, seems to be assuming a particular SQL product, which may not necessarily be the one the OP is using. – Andriy M Feb 21 '13 at 23:23