Hi
I have a table using autoincrement in column 1 (primary key).
And i have a button to reload the table. The button deletes all the rows from the table and Re-add them into the same table.
i use protocol.NotifyProtocol(156 /*Delete row*/, tableID, Pkey) to delete the rows from the table.
Then i use protocol.NotifyProtocol(149 /*Add new row*/, tableID, null) to re-add and use SetParameterIndex to fill the cell of the row.
The delete row and re-add row works all fine.
However, the only problem i have is:
seems the primary key (autoincrement) somehow didn't get reset to 1 when i delete all the rows from the table. When i re-add the rows in, the primary key value increase from the last deleted value.
e.g.,
I added 4 rows. the autoincrement primary key will be:
1,
2,
3,
4,
Then I reloaded the table, all 4 rows were deleted from the table. And the 4 rows were re-added into the same table. But the primary key start from 5.
5,
6,
7,
8
How can i reset the autoincrement primary key back to 1 when i delete all the rows from the table?
Thanks
Hi,
I did not test this, but it might be possible when all the rows are removed and when then restarting the element that the primary key will start again from 1.
Do note that an auto increment primary key is just something that is used as a unique identifier when there are no other identifiers possible. Meaning if the primary keys are re-used that they could be linked to past unrelated data.
In other words, if there is a column that has trending active on a row and the table gets cleaned up and the primary key restarts, that when a new row is added to this same key this means when requesting the trend graph will show the old data and the new data. Other items, like the AI prediction, also rely on the primary key identifier and could be making wrong predictions because the data is not related to each other.
Regards,
Hi,
If Laurens' suggestion doesn't resolve the issue, you can consider implementing a custom auto-increment mechanism to achieve your desired behavior.
When adding rows to the table, you can track the current counter and increment it by 1 for each new row.
To determine the current count, you could:
1. Retrieve all primary keys from the table and calculate the count dynamically, or
2. Store the counter in a hidden parameter and update it as needed.
When deleting all rows from the table, ensure you reset the counter to zero so it starts fresh when re-adding rows.
I hope this helps!