0

I want to delete a row in java derby table without any affect in primary key. For example, if the rows are

pkey item price
1 apple 100
2 banana 20
3 avocado 55

then when I delete the 2nd row , it shows

pkey item price
1 apple 100
3 avocado 55

but I want to show it as

pkey item price
1 apple 100
2 avocado 55

Arun B R
  • 21
  • 7
  • 6
    No, really, really, you don't want that. All the rows, files, applications, bookmarks using that PK to reference the row would now be broken. That's why a PK is supposed to be immutable. Get over it: a PK is not a monotonous sequence number. – JB Nizet Aug 19 '18 at 12:07
  • 1
    If you want your user interface to show a continuous sequence of row numbers, then you can just generate one when the result set is retrieved and keep the actual primary key values hidden. Separate the _presentation_ from the data and you'll save yourself from many problems. – Mick Mnemonic Aug 19 '18 at 17:43
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. Also, you are not clear. What does "without any affect in primary key" mean? What is your "example" an example of? – philipxy Aug 19 '18 at 23:04
  • Is it possible to delete the pkey coloumn and then add a new coloumn instead.In that case, is it auto generate index from 1. ? – Arun B R Aug 22 '18 at 04:01
  • Please edit clarifications into your question, not comments. If you have a new question & can't find an answer then please post a new question. – philipxy Aug 22 '18 at 06:03
  • Possible duplicate of [How to represent and insert into an ordered list in SQL?](https://stackoverflow.com/q/2940476/3404097) – philipxy Aug 22 '18 at 06:06

2 Answers2

0

Of course no ! You cannot update Primary Key in all case. The same primary key after deleting, cannot be used twice, it is one of main rules relational databases.

You can create your own number value, and after removing shift it.

ImZ
  • 46
  • 1
  • 9
  • how can I shift it if there are so many rows . may I need to update each row is not possible in that case – Arun B R Aug 19 '18 at 12:15
  • I cannot imagine situation when this is needed. Could you please describe, your issue. – ImZ Aug 19 '18 at 12:29
  • I want to make a list of items to be shown in a form.It shows the old values from the database.User can edit the items as well as delete. i completed the edit code of the list. but when they delete an item , the primary key will be incorrect as there is ommission of a middle row number. It will effect the edit option also. – Arun B R Aug 19 '18 at 12:48
  • Okey, but if you want to add number of order, you can add it in cycle, when you retrieve list from db. – ImZ Aug 19 '18 at 12:52
  • can I Update the primary key to change the values – Arun B R Aug 19 '18 at 12:54
0

You can not update or renumber PK

Instead, I recommend to manage the renumbering action in your java code (not in database level), since even after deleting a record, you still have PK of the remaining records in a sorted way.

Iman Nia
  • 2,255
  • 2
  • 15
  • 35