Apparently, I am unable to have more than one auto-incrementing field.
I currently have a key field identified as such and am trying to make another field auto-incrementing as well. Is this a no-no with Librebase?
Base is no database, but connects to databases. So the database you use needs to support your feature.
.
Only one auto-incrememt is quite common as you may see at the following link for MariaDB. As I guess several auto-increments will be related you could calculate the others, when you have one. So the are not needed…
AUTO_INCREMENT - MariaDB Knowledge Base.
A autoincremented field is used for primary key. So it isn’t possible to create more than one autoincremented field for a table. What do you want to get with a second field showing the same behavior as the first field?
In a single table of an hsqldb database of LO base, only one Auto-increment is possible, usually used in a integer Primary Key field. However, it is relatively easy to implement an auto entered integer “count” field from the result of a macro triggered on an event of the form or grid. Count the number of records using a select count(*) sql where the distinguishing criteria (value, expression) exists in another field of the underlying table. The macro then auto-saves the result to the “count” integer field in the underlying table. Warning! If records of this table are deleted or changed, then gaps or duplicates will appear in the “count” field. Use an sql Max(count) function plus 1 (+1) result in the macro to avoid duplicates. Here is the basic sql:
- “SELECT Count(*), Max(”“count”") As ““maxCnt”” FROM ““table”” WHERE ““textField”” = ‘someText’ AND ““numericField”” = 1.2345"
Sky,
I now understand that I can implement only one auto-incrementing field and its for the primary key.
It also seems that the actual primary key “numbers” are hidden and differs from the number shown in that field, though the order of the numbers (barring missing numbers) is shown correctly. Do you know where this correlation/issue is discussed?
I need a field with incrementing numbers because my primary key numbers are wacko.
In my application, there is no criteria for the count, just the existence of the record, so the first rec would be 1, second rec would be 2 and so forth to the end of the table. When I add a record, it’s “counter” field would increment by one.
- Do you have an example of a macro that would do this?
- What event should I call it from?
- The table I would like to add the “count” field to already exists. Is there a way to fill the count field from 1 to n for the existing recs?
Thanks, Erik
Many databases will get the row number by a window-function. Firebird or MariaDB will do it. But the old internal HSQLDB can’t. This query will help:
SELECT "a".*,
( SELECT COUNT( "ID" ) FROM "MyTable" WHERE "ID" <= "a"."ID" )
AS "RowNr"
FROM "MyTable" AS "a"
ID should be the field for a primary key.
the primary key could be autoincremented. It starts with ‘0’ in HSQLDB, ‘1’ in Firebird. It will show the rownumbers also if you don’t delete rows.
Just a quick help… the following is a simple method to disable the “bad” Primary Key field in order to add a populated, sequential, and gapless “ID” field, then afterwards reset the table to use the “bad” Primary Key to preserve original relations to foreign keys. This method may appear tedious, but because most of it involves important “save” operations, it goes quickly. I use the Win keyboard shortcut (ctrl+s) for quick saves of the odb and for each component.
- In Relationships Tool (LO main window - Tools / Relationships), delete all relations between the bad Primary Key (“BadID”) in the badTable and all related foreign keys. Save the relations! Save the odb!
- In the odb Tables list, edit the badTable and Unmark “Primary Key” from “BadID” (right click far left row marker), then change “BadID” field name to something like “oldBadID”. Select any other field in the table and then save and close table! Save the odb!
- Drag and drop “badTable” with mouse to a blank area at the end of the table list.
- In the prompt window, name “badTable2”, select the “Definition” option only, then click “Create”. Do NOT create a new primary key. the table “badTable2” should now appear in alphabetic order in the Table list. Save the odb!
- Edit “badTable2” and add new integer field (“ID”) and mark as “Primary Key”. Save the table! Save the odb! After saving, set the “ID” field auto increment property to Yes, then after selecting any other field in the table, save and close the table! Save the odb!
- Now drag and drop “badTable” directly onto “badTable2”. In the prompt, name “badTable2”, select only “Append data” option, then “Create”. Do Not create new primary key. Save the odb!
- “badTable2” should now contain all data of “badTable”, plus the new incremented “ID” field. Edit badTable2 and Unmark “ID” as “Primary Key” (right click left row marker). Save the table! Save the odb! After saving, mark “oldBadID” field as “Primary Key” (right click left row marker). Save the table! Then set “oldBadID” auto increment property to Yes! After selecting any other field of the table, save the table! Save the odb!
- In Relationships Tool, recreate the relations from “badTable2”.“oldBadID” to all its related foreign keys of child tables. Save and close relations! Save the odb!