I have created a form with a table control element which is used to input data to a table. Some of the fields have "input required" enabled, so when I want to jump to another row (could also happen by clicking somewhere else by mistake) before I entered any data into those fields, Base shows the correct error message and lets me return to that row to input the missing data. However, it still increments the auto-value of the primary key.
So let's say, for example, the previous table entry has ID=5. -> I start a new row -> mess up while inputting the data or forget one field -> that error message is shown -> I correct my input -> continue to the next row, the row will now have ID=7 instead of ID=6.
(This is the error message: firebird_sdbc error:
*validation error for column "tablename"."fieldname", value "*** null ***"
Steps to Reproduce:
1. Create a table
2. Create a primary key with type integer and auto-value enabled
3. Create a field of any type without "input required"
4. create some other field with "input required"
5. Save the table
6. edit the table: enter something into first field (the one without input required)
7. leave the second field blank (the one with input required)
8. try to get to the next row by using tabulator key
9. Error message pops up because of blank field. Exit message
10. enter data into second field (the one which was previoulsy left blank)
11. tab into next row by using tabulator key
12. Primary key integer value is now 2 whereas it should be 1
The Primary key value should not be increased when the data record submission was aborted due to an error
User Profile Reset: No
Version: 220.127.116.11 (x64)
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win;
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Created attachment 152232 [details]
See "Table1" in the attached database - not NULL for field Surname
The buggy behaviour isn't a buggy behaviour of the form. It could also be reproduced in a table.
Try to insert content in the table "Table1" without filling "Surname" with content. It will fail with an error from Firebird - but the counter for "ID" will count everytime I try to submit the data.
I`m not sure it is a bug,
Firebird use transactions, Some explanation http://docs.openlinksw.com/virtuoso/identity/.
On the other hand, if I'm not wrong, basic use of auto increment columns are for to have a primary key for the table, so records have unique IDs on the table.
FWIW, I seem to recall that the same behaviour occurs when working with postgresql and an autovalue field. I also seem to recall reporting this at the time, and it being deemed not a bug...(but I can't find the bug report at the moment).
autoincrement fields have unique values, but are not necessarily strictly sequential.
That is indeed so in several different DMBS. It is kinda hard (not sure even possible) to ensure the values are strictly sequential, if one allows transactions (that can be cancelled...) and/or concurrent inserts.