Update Autonumber field after DELETE query

Question

INTRODUCTION AND RELEVANT INFORMATION:

I have MS ACCESS 2007 database that I edit using ADO and C++.

PROBLEM:

My problem is that primary key also represents an ordinal number of the record, and after deletion, it should be properly updated. Primary key is of autonumber type.

Here is an example of what I am talking about:

| #PK | Other data ... |
|  1  |      ...       |
|  2  |      ...       |
|  3  |      ...       |
|  4  |      ...       |
|  5  |      ...       |

Now if I delete the 3rd record I get the following problem:

| #PK | Other data ... |
|  1  |      ...       |
|  2  |      ...       |
|  4  |      ...       |
|  5  |      ...       |

but I should get the following result:

| #PK | Other data ... |
|  1  |      ...       |
|  2  |      ...       |
|  3  |      ...       | // edited to reflect the change ( previous value was 4 )
|  4  |      ...       | // edited to reflect the change ( previous value was 5 )

If I delete last record and then insert new one I get this result:

| #PK | Other data ... |
|  1  |      ...       |
|  2  |      ...       |
|  3  |      ...       |  
|  4  |      ...       | 
|  6  |      ...       |  // this should be 5

QUESTIONS:

Is there a way for me to programmatically update the autonumber field after I perform the DELETE query ?

EDIT:

Since I am aware this is a bad practice, I would prefer adding new field that should be ordinal number so my table can look like this:

| #PK | Ordinal | Other data ... |
|  1  |    1    |      ...       |
|  2  |    2    |      ...       |
|  4  |    3    |      ...       |  
|  5  |    4    |      ...       |

but I would prefer it to update itself automatically. If this is not possible, I would prefer to update the field with SQL query after I perform the deletion.

Thank you.

Best regards.

Racil Hilan · Answer 1 · 2014-04-11T23:45:14.113

3

It is possible, but not the right way. Primary keys are used for relationships, so if you change the values, you need to update all related tables. Even if you currently don't have any related tables, you still should consider adding a separate field for the order, otherwise you may face the same problem in the future when you want to add related tables.

EDIT To answer your question:

Is there a way to add another field that would represent ordinal number and will automatically increment after inserting new record?

If you set it to autonumber, it will automatically increment, but you will not be able to modify it. You can set it to number and when you insert, you use SELECT MAX(oredinal) + 1 FROM mytable to increment it.

edited Apr 11 '14 at 23:45

answered Apr 11 '14 at 23:27

Racil Hilan

24,690
13
50
55

Since I agree with you, I am asking you to help me with my second approach. Is there a way to add another field that would represent ordinal number **and will automatically increment** after inserting new record ? That way I could update this field **after I perform deletion.** Thank you. Best regards. – AlwaysLearningNewStuff Apr 11 '14 at 23:33
1

Please do not use Max to get the next sequential number, it is quite likely to lead to duplicates in a multi-user system. You will need a seed table that can be locked to get the next number. Here is a VBA example http://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647 – Fionnuala Apr 12 '14 at 00:07

score 1 · Answer 2 · answered Apr 11 '14 at 23:25

For MS Access use

ALter Table Customer alter column CustomerID Counter(1,1)

For Sql Server

DBCC CHECKIDENT (orders, RESEED, 0)

This will set the value of the next ID to be 1, you can use above command.

Ref URL@ http://www.howtogeek.com/howto/database/reset-identity-column-value-in-sql-server/

score 0 · Accepted Answer · answered Apr 17 '14 at 16:32

I have decided to add a new field in my table that will hold the ordinal number of the record.

If we assume the field is named OrdinalNumber then the following solution worked for me:

// when inserting record, I just had to add COUNT( PK ) + 1
INSERT INTO MyTable (  OrdinalNumber , ... ) SELECT COUNT( PK ) + 1 , ...  
    from  MyTable ;

// when deleting, I had to perform following two queries :
DELETE from MyTable where PK = ? ;

// decrement all the successors ordinal number by one
UPDATE MyTable set OrdinalNumber = (  OrdinalNumber - 1 ) where ( PK > ?  );

Everything seem to work well. I wish there was an easier way though...

Thanks everyone for helping. I have upvoted all the answers.

Update Autonumber field after DELETE query

INTRODUCTION AND RELEVANT INFORMATION:

PROBLEM:

QUESTIONS:

EDIT:

3 Answers3