Click here to Skip to main content
15,844,387 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day please I am creating a cbt app, anything I delete a question and insert another question it id does not decrease. Example, I have 10 questions after deleting it I expect the next questions I insert should start from 1 but it start from 11. Please help

What I have tried:

I have searched through the net but no answer
Posted
Updated 22-Sep-17 21:01pm
Comments
PIEBALDconsult 22-Sep-17 22:17pm    
0) Don't delete.
1) Don't use Identity/autoincrement.
Dave Kreskowiak 22-Sep-17 22:35pm    
If you're using identity or autoincrement as something to maintain order of items, you're not doing this correctly. Ordering of items should be a separate field as an integer or something appropriate.

Autoincrement ID values do NOT get recycled and reused.

1 solution

From your description, you are using an IDENTITY column - that is the expected behaviour, and it is actually what you want to happen. The problem is that you are assuming that ir represents a row numbering system, when it represents a unique ID value for each row instead.

The database doesn't "recycle" IDENTITY values, because it doesn't "know" what you are using them for - so it assumes that you are referring to those values from other places and other data. If it recycled them, then the old data would effectively become attached to the new rows, and that could be very bad indeed. Imagine an invoicing system where purchases for a different, deleted, customer became attached to your new customer and your were charged for them!

While it is possible to reset an identity field:
SQL
DBCC CHECKIDENT('mYTable', RESEED, 1)

It's a bad idea, generally.

So don't use it as a row number: instead, use ROW_NUMBER:
SQL
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber, ID, QuestionText FROM MyTable
 
Share this answer
 
Comments
akinwunmi 28-Sep-17 20:06pm    
Thanks that means i should create a column for the id but what if the user mistakely enters the same Id?
OriginalGriff 29-Sep-17 2:14am    
The user doesn't enter the ID, the whole idea of an IDENTITY column is that it is maintained by the database to ensure it is unique. Did you tell your bank what your account number should be when you opened your bank account? Of course not! The bank allocated one when you signed the forms and sent that info to you later along with a bank card, a cheque book, a welcome pack, or whatever. Users only specify an ID when they know it, and that's after it's been created by the system.
akinwunmi 28-Sep-17 20:09pm    
SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber, ID, QuestionText FROM MyTable
please where am i going to paste this code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900