0

I am new to MySql and coding. i have an Aid column that is Primary Key and Auto Increment in settings. But while I delete a row it doesn't update the Aid.

I know that the code that i need in MySql is :

SET @a = 0;
UPDATE aircraft.l1201 SET l1201.Aid = @a := @a +1;

How can I translate this into c# code?

Florian Koch
  • 1,372
  • 1
  • 30
  • 49
guetty abboud
  • 31
  • 2
  • 7
  • Please clarify your problem. What do you mean by "while i delete a row it doesn't update the Aid"? AutoIncrement will add an ID for new rows, but deleting won't affect the counter at all. – Florian Koch Jul 05 '17 at 08:05
  • here is an example: Aid 1 2 3 if i delete row 2 the Aid becomes : Aid 1 3 and the added row will have Aid = 4. what i want is to auto update the ID value each time – guetty abboud Jul 05 '17 at 08:09
  • so you want all successive entries to be changed when the row is deleted, as well as the counter of AutoIncrement to be decreased? This isn't a good idea, just imagine you have thousands of rows and the first one ist deleted, this would result in all other rows being edited. Also, it's never a good idea to change the ID of an existing row (references might break or have to be cascaded). Why do you want this? – Florian Koch Jul 05 '17 at 08:22
  • refer to [this answer](https://stackoverflow.com/a/2214209/3326982) for more information regarding the topic – Florian Koch Jul 05 '17 at 08:29
  • the ID is just a number i'm using to be able to select the deleted/updated data. there are no other use for it. It is more presentable that the IDs are successive. Or what do you suggest ? – guetty abboud Jul 05 '17 at 08:29
  • I suggest you let AutoIncrement work like it is intended. There is no problem if the IDs are not successive, except that it "doesn't look good". IDs are something to work with, not really to look at. – Florian Koch Jul 05 '17 at 08:31
  • Possible duplicate of [Auto Increment after delete in MySQL](https://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql) – Florian Koch Jul 05 '17 at 08:36

1 Answers1

0

I recommend you don't try changing the default behaviour of AutoIncrement. Changing all IDs after deleting a row doesn't bring you any real advantages, but a lot of disadvantages, including a potentially high amount of update operations after every delete as well as the risk of destroying referential integrity (in case ON UPDATE CASCADE isn't used).

Refer to this answer on a related question, it sums up the topic quite well.

Florian Koch
  • 1,372
  • 1
  • 30
  • 49