B4J Question [Solved] SQLite Autoincrement Problem

behnam_tr · Nov 10, 2023

hi guys,

I have a database table that has a field called ID and the field is autoincrement.
Suppose it has 5 records whose IDs are from 1 to 5 respectively
Now I insert a record manually with ID 10
Next time when inserting a new record, it will automatically insert the number 11
I want to continue from number 6 for new recods. Is there a solution??

sorry for my bad english!
-------------------------
database :
tbl1 >> id,name

records :
1,"any name"
2,"any name"
3,"any name"
4,"any name"
5,"any name"
10,"any name"
------------------------

BlueVision · Nov 10, 2023

I don't really understand your problem. What does this have to do with the ID? The ID is just an internal marker for the database itself. How do you establish the relationship between the ID and the product code? The two have nothing to do with each other...
It doesn't really matter what ID the data record of a product code has, the important thing is the product code itself. Or are you using the ID as the product code? In my opinion, that would be a design error in the SQL database.
You search the SQL database using queries. There, too, the ID of a data record only plays a subordinate role. It does not belong to the actual data record. Why search for the product code via the IDs?
Please try to explain your problem to me a little...

BlueVision · Nov 10, 2023

Really a problem or just "cosmetics"?

Is it really necessary to change the ID? No. The ID is usually a primary key and not really a number, just a unique identifier when reading the data from the table to map a continuous sequence within the table. The ID does not usually belong to the data itself that you want to read from the table. In reality, this series of ID's will never really be intact, you should not bother to make this series "pretty". The only purpose of a primary key is to keep a database row forever so that you can refer to rows from other tables.

The easiest way to reassign new primary keys in ascending order would be as follows:

1. rename the original table.
2. create a new table with the original name
3. then read data record by data record sequentially from the original table, then immediately insert this data into the newly created table with the ID "null". SQL interprets a data record with the ID "null" as a new data record and inserts it at the end of the new table with the next free ID.
4. drop the original table, first renamed table.

This would generate an uninterrupted series of IDs in the new table.

behnam_tr · Nov 10, 2023

BlueVision said:
Really a problem or just "cosmetics"?

Is it really necessary to change the ID? No. The ID is usually a primary key and not really a number, just a unique identifier when reading the data from the table to map a continuous sequence within the table. The ID does not usually belong to the data itself that you want to read from the table. In reality, this series of ID's will never really be intact, you should not bother to make this series "pretty". The only purpose of a primary key is to keep a database row forever so that you can refer to rows from other tables.

The easiest way to reassign new primary keys in ascending order would be as follows:

1. rename the original table.
2. create a new table with the original name
3. then read data record by data record sequentially from the original table, then immediately insert this data into the newly created table with the ID "null". SQL interprets a data record with the ID "null" as a new data record and inserts it at the end of the new table with the next free ID.
4. drop the original table, first renamed table.

This would generate an uninterrupted series of IDs in the new table.

Thank you.
Yes, there is definitely a problem for me.
To insert the product code in the database, I want to give the user the possibility to enter the desired ID, and of course, on the user's side, I check whether the ID is duplicated or not.
I think the simplest and most basic solution is to find the first number that has a difference of more than 1 unit between the previous and the next number.
In this example, the number will be 5 because the difference between 5 and 10 is more than 1 unit. In fact, the first empty space is found.
But I am looking for a better solution if there is one

BlueVision · Nov 10, 2023

I don't really understand your problem. What does this have to do with the ID? The ID is just an internal marker for the database itself. How do you establish the relationship between the ID and the product code? The two have nothing to do with each other...
It doesn't really matter what ID the data record of a product code has, the important thing is the product code itself. Or are you using the ID as the product code? In my opinion, that would be a design error in the SQL database.
You search the SQL database using queries. There, too, the ID of a data record only plays a subordinate role. It does not belong to the actual data record. Why search for the product code via the IDs?
Please try to explain your problem to me a little better. I need to understand exactly what you are trying to do.
Can you show the structure of the table? The best way to do this is to open the database with an SQL viewer. You can make the data itself unrecognisable. Only the structure of the database itself is interesting.

Edit:
Assuming, you are using the ID of a data record as the product code. The design of the database should be changed.
The database design itself should look somehow similar like this:

ID, ProductCode, ProductDescription, QuantityOnHand....

Maintenance, updating, searching, replacing of records then are quite easy to handle with SQL functions without a relation to the ID.

MicroDrie · Nov 11, 2023

There are written and unwritten rules on how to design a database and how it works.

has a field called ID and the field is autoincrement

So you actually want to propose a suggestion that the user can deviate from. That approach is simply contrary to an autoincrement setting. For a simple database design with just a single table, your approach might work by including a unique number product code in the ID field.

But what if your product gets a new version in which, for example, you have to add Mark2 as M2 to the product number in an autoincrement field? If it uses a field name of, for example, ProdNum, then you have the freedom to include numbers and characters, while you can simply use the ID field to refer to a specific record in another table, for example, by creating a relationship based on of the unique ID number.

I think the simplest and most basic solution is to find the first number that has a difference of more than 1 unit between the previous and the next number.

Again this is not in-line how an autoincrement works, therfore you must use a additional productnumber field to do what you want.

aeric · Nov 11, 2023

Not very clear what you are trying to do.
You mean you want to insert new row which would get ID of 6 without specifying it.

What happen when you try to add a new row which will conflict with ID 10? You will get an error.

If you insist, I think you can change the id in sqlite_sequence table.

behnam_tr · Nov 11, 2023

BlueVision said:
I don't really understand your problem. What does this have to do with the ID? The ID is just an internal marker for the database itself. How do you establish the relationship between the ID and the product code? The two have nothing to do with each other...
It doesn't really matter what ID the data record of a product code has, the important thing is the product code itself. Or are you using the ID as the product code? In my opinion, that would be a design error in the SQL database.
You search the SQL database using queries. There, too, the ID of a data record only plays a subordinate role. It does not belong to the actual data record. Why search for the product code via the IDs?
Please try to explain your problem to me a little better. I need to understand exactly what you are trying to do.
Can you show the structure of the table? The best way to do this is to open the database with an SQL viewer. You can make the data itself unrecognisable. Only the structure of the database itself is interesting.

Edit:
Assuming, you are using the ID of a data record as the product code. The design of the database should be changed.
The database design itself should look somehow similar like this:

ID, ProductCode, ProductDescription, QuantityOnHand....

Maintenance, updating, searching, replacing of records then are quite easy to handle with SQL functions without a relation to the ID.

Yes, I use the ID as a product code!

behnam_tr · Nov 11, 2023

aeric said:
What happen when you try to add a new row which will conflict with ID 10? You will get an error.

with this query : "INSERT INTO kala (?, ?, ?, ...) VALUES (null, value2, value3, ...)"
The ID continues from number 11
Of course, I said that I will check for duplicates, and if the user tries to enter 10 or any duplicate number again, I will prevent it.

behnam_tr · Nov 11, 2023

BlueVision said:
I don't really understand your problem. What does this have to do with the ID? The ID is just an internal marker for the database itself. How do you establish the relationship between the ID and the product code? The two have nothing to do with each other...
It doesn't really matter what ID the data record of a product code has, the important thing is the product code itself. Or are you using the ID as the product code? In my opinion, that would be a design error in the SQL database.
You search the SQL database using queries. There, too, the ID of a data record only plays a subordinate role. It does not belong to the actual data record. Why search for the product code via the IDs?
Please try to explain your problem to me a little better. I need to understand exactly what you are trying to do.
Can you show the structure of the table? The best way to do this is to open the database with an SQL viewer. You can make the data itself unrecognisable. Only the structure of the database itself is interesting.

Edit:
Assuming, you are using the ID of a data record as the product code. The design of the database should be changed.
The database design itself should look somehow similar like this:

ID, ProductCode, ProductDescription, QuantityOnHand....

Maintenance, updating, searching, replacing of records then are quite easy to handle with SQL functions without a relation to the ID.

ID, ProductCode, ProductDescription, QuantityOnHand....
I think it is a logical and correct solution and I have to redesign the table.
My goal was to solve the problem without pain and bleeding!!

aeric · Nov 11, 2023

Then there is no point to make the ID column as autoincrement.

behnam_tr said:
with this query : "INSERT INTO kala (?, ?, ?, ...) VALUES (null, value2, value3, ...)"
The ID continues from number 11
Of course, I said that I will check for duplicates, and if the user tries to enter 10 or any duplicate number again, I will prevent it.

behnam_tr · Nov 11, 2023

aeric said:
Then there is no point to make the ID column as autoincrement.

Yes, I came to the conclusion that if I want the user to be able to enter a desired ID, I should add a separate field

aeric · Nov 11, 2023

I think many programmers has a misconception of autoincrement and always use null to insert the ID.

I always write my INSERT statement without the ID, so I never has this problem of specifying the ID or not.

aeric · Nov 11, 2023

behnam_tr said:
Yes, I came to the conclusion that if I want the user to be able to enter a desired ID, I should add a separate field

I will use code e.g product_code or SKU instead of ID to avoid confusion.

BlueVision · Nov 11, 2023

behnam_tr said:
I think it is a logical and correct solution and I have to redesign the table.
My goal was to solve the problem without pain and bleeding!!

Exactly. Regardless of where the existing data record is located in the table, your programme can then react flexibly. How you then display this is of course up to you. Depending on the size of the database, it makes perfect sense to display this data in the form of a B4XTable (you can do without the ID... ). Or you can develop your own form for displaying the data. The user of your programme would then, for example, only have to search for the product code via the search field of the B4XTable; if this already exists, the data record is simply updated or, if not available, newly created. SQL then performs these queries very quickly in the background. For sure, I personally would keep the incremental ID of a single record. It's a "passport" for a single database record. If there are gaps in between, who cares?
I am absolutely sure you will find suitable ways. If not, please get in touch! Keep it simply und comfortable for the user. Use click functions within the B4XTable to select a record and for editing. Most of it, can be done without typing text (increment or decrement the amount on stock and so on). Sure, if a new product has to be entered, the user needs to type the name in. So it will create user satisfaction. The user needs the feeling, that he needs your comfortable programming solution, asking himself how to live without before.
And the possibilities are great. You can give the user the functions he needs (supporting counting whats on stock and what has to be ordered and so on). You can create warnings, if there is some item low on stock. Simply another value within the table to read out (minimum stock). Just my ideas.

Believe it is very satisfying for you, when the customer loves your product. And this value, he gives back to you then. If you are able to react flexible to the needs of the customer, adding by time more and more suitable functions for the customer, the customer will be happy. So it is very important to have a good concept.

Additionally, keep in mind that in future your database must be extendend. So a version check is very important if the structure of the database will change in future. Honestly said, I am not the great SQL-developer. I do most of that by using DBUtils. DBUtils will support you as a programmer in accessing the database with very good and prooved routines for accessing. Maybe it needs some milliseconds more during run. Who cares? But you don't have to think about accessing the database itself. There are also some routines built in for version checking. Just an idea...

MicroDrie · Nov 11, 2023

Requirements doesn't always fit to the "Keep It Super Simple (KISS)"principle. Your challenge is described as a "Gaps and Islands" article in "Identify non-consecutive values in SQL Server". Looking to this result:

Based on this query result, you can suggest the value for the first NULL value to use the value_of_interest + 1 as a suggestion to fill the cap.

netsistemas · Nov 13, 2023

I think you shouldn't use an autonumber field.
In many systems, it is also not allowed to insert into the autonumber field (or by resorting to tricks).
The best thing would be for you to do a select MAX and you manage the autonumber.
If you take advantage of breaks, you must save yourself somewhere, in the field that that counter goes and manage it.
In the end, you will have to manage your jumps, so the best thing is that you have absolute freedom to do what you want, which will lead you to not use autonumbers in this case.

Magma · Nov 13, 2023

behnam_tr said:
hi guys,

I have a database table that has a field called ID and the field is autoincrement.
Suppose it has 5 records whose IDs are from 1 to 5 respectively
Now I insert a record manually with ID 10
Next time when inserting a new record, it will automatically insert the number 11
I want to continue from number 6 for new recods. Is there a solution??

sorry for my bad english!
-------------------------
database :
tbl1 >> id,name

records :
1,"any name"
2,"any name"
3,"any name"
4,"any name"
5,"any name"
10,"any name"
------------------------

.... Hmmm, ok may be it is wrong... but it can be..

1. I think the way it will be first check how many records you have... (number of records) and then id will be (totalrecords+1)... but,
2. Then check if there is already record with this ID... if there is --- ask user --- or take this ID and add +1 to propose ID...

but the best will be this:
... But if is only numeric (perhaps int) value, you may be order by ID... read one by one all records... and check the difference of ID with the previous... if the difference is bigger than "1" then you found a deleted record... so the propose will be that (previous ID +1) !... if not found you must propose the (total count of rows)+1

ps: also have in mind replacing old IDs with new IDs records... is also dangerous for our correlation of data...

MicroDrie · Nov 13, 2023

Back to the original question:

I want to continue from number 6 for new recods. Is there a solution??

The SELECT query in lines 26 to 31 in the SQLite commands below returns the first available product numbers of all existing gaps in the range of product numbers in the existing product range in DB Browser for SQLite:

Create demo database and find first free productnumbers:

-- delete only existing table
DROP TABLE IF EXISTS "products";

-- Create new table
CREATE TABLE "products" (
    "ProductCode"    INTEGER UNIQUE,
    "name"    TEXT UNIQUE
);

-- Create table contents
INSERT INTO products (ProductCode, name)
VALUES
    ( 1, "Product_01"),   
    ( 2, "Product_02"),   
    ( 3, "Product_03"), -- first gap
    ( 6, "Product_06"),
    ( 7, "Product_07"), -- second gap
    ( 9, "Product_09"), -- third gap
    (11, "Product_11"),
    (12, "Product_12"), -- fourth gap
    (20, "Product_20"),
    (25, "Product_25");
   
-- Get all frist free producnumbers   
SELECT A.ProductCode + 1
FROM products AS A
WHERE NOT EXISTS (
    SELECT B.ProductCode FROM products AS B
    WHERE A.ProductCode + 1 = B.ProductCode)
GROUP BY A.ProductCode;

Mahares · Nov 13, 2023

MicroDrie said:
Back to the original question:

The OP already has his solution, but this exercise has become interesting. Here is a query that yields the same result as the one in post #18:

B4X:

SELECT (ProductCode + 1) AS PPLUS FROM products WHERE PPLUS NOT IN(SELECT ProductCode FROM products)

aeric · Nov 14, 2023

aeric said:
If you insist, I think you can change the id in sqlite_sequence table.

Anyone care to check this?

B4J Question [Solved] SQLite Autoincrement Problem

Active Member

Active Member

Active Member

Active Member

Well-Known Member

Expert

Active Member

Attachments

Active Member

Active Member

Expert

Active Member

Expert

Expert

Active Member

Well-Known Member

Active Member

Expert

Well-Known Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency