none
Deleting rows (where P.K. autoincrement=true) RRS feed

  • Question

  • HI. I want to delete rows from myTable. I know how to do it but some problems come out while doing it.

    1. For example I have 4 rows in myTable

    ID      |    Name

    1       |   Jack

    2       |   Mike

    3       |   Mery

    4       |   Linda

    Now when i delete for example row(4,"Mike") and then add a new row the ID for that row is 5(ID is generated automatically as its autoincrement=true), but must be 4...

    What can I do?

    2. What if i want to delete row(3,"Mery") and add a new row, where it will be placed and what ID it will have?

    Wednesday, September 14, 2011 7:05 PM

Answers

  • If you need to keep the auto-increment, and you don't want to skip IDs when you delete, then I suggest you don't actually delete the rows, but have an IsDeleted bool column in your DataSet and simply set that to true when you want to delete (and perhaps also clear out all of the data except the PK). The next time you add a row, see first if there are any IsDeleted rows that you can re-use. Will that work for you?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Thursday, September 15, 2011 2:22 PM
  • Well in that case you need to re-sequence the complete table.. In the mean while you can't do it while the auto increment property is set to true. Try manually setting that PK value(Instead of using auto increment...!).

    the auto increment will be helpful only when you want a value that should not repeat again in the lifespan of the Table(Like, Identity in TSQL).


    - <BooM>
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Friday, September 16, 2011 2:35 PM
  • You could write a method that does that for you, resequencing from the point at which you deleted. But, to be honest, this really isn't how ID's should be used. If Linda's ID is 4, it should remain a 4. Let me ask you this ... what is the purpose of your ID column? How is it being used in your app?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Friday, September 16, 2011 3:32 PM

All replies

  • If you need to keep the auto-increment, and you don't want to skip IDs when you delete, then I suggest you don't actually delete the rows, but have an IsDeleted bool column in your DataSet and simply set that to true when you want to delete (and perhaps also clear out all of the data except the PK). The next time you add a row, see first if there are any IsDeleted rows that you can re-use. Will that work for you?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Thursday, September 15, 2011 2:22 PM
  • Thanks, but i want something other. What if i want just to delete some row(for example row(3,"Mery")).In that case i would like to have 1.Jack, 2.Mike, 3.Linda(but i get 1.Jack, 2.Mike, 4.Linda);Isn't there any function that i could reset P.K.?
    Friday, September 16, 2011 12:46 PM
  • Well in that case you need to re-sequence the complete table.. In the mean while you can't do it while the auto increment property is set to true. Try manually setting that PK value(Instead of using auto increment...!).

    the auto increment will be helpful only when you want a value that should not repeat again in the lifespan of the Table(Like, Identity in TSQL).


    - <BooM>
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Friday, September 16, 2011 2:35 PM
  • You could write a method that does that for you, resequencing from the point at which you deleted. But, to be honest, this really isn't how ID's should be used. If Linda's ID is 4, it should remain a 4. Let me ask you this ... what is the purpose of your ID column? How is it being used in your app?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by artxach Monday, September 26, 2011 6:55 PM
    Friday, September 16, 2011 3:32 PM