I like to use an integer for the primary key in many of my tables. I call the field ID usually.
In other database engines, I’ve set things up so that the field is filled in automatically for me (with the next available integer) whenever I create a new record.
So far, attempts to do that in Firebird have failed. And searches suggest that it isn’t easy. And I regard “not easy” “solutions” as risky, potentially unreliable. (Environment details below.)
So! Every table must have a primary key. What to do?
I’m afraid that after hours of trying to achieve a reliable auto-increment, I have thrown in the towel.
I set up my tables with a field called “ID”. I define it as holding type-integer data. I set that field as the table’s primary key.
Then, from time to time, I create, by hand, a bunch of otherwise empty records, each with a unique integer in the ID field. I then use those “shells” when I want to add a record to the database.
Tedious. But Gets The Job Done. Sigh.
(You can, of course, just enter all of each new record by hand, one at a time. But the method below gives you a way to “pre-fill” any fields that you wish with default values, too.)
===
For the “lazy”…
If you can’t stand the tedium of that “solution”, and have a taste for the arcane…
Build a simple spreadsheet with a line for each record. The spreadsheet, for the simplest version of this, will have just one column.
This technique would also serve, if you wanted to fill other fields at the same time as putting values in the new records that are being created on the fly. Just be sure to put something in the primary key’s field at the same time.
Here’s how…
A one column spreadsheet with the following would add new, otherwise blank, records with ID’s 1000, 1001, 1002, etc…
1000
1001
1002
1003
1004
1005
1006
1007
1008
(You must be careful not to create a second record with the same ID as that of a record that already exists.)
The values can be produced with formulae, by the way. (Put 1000 into cell A1 by hand, then put =A1+1 in A2. Then copy/paste A2 down the page. A3 will be =A2+1. A4 will be =A3+1, etc.
Select all the numbers.
Press ctrl-c, to copy what you have selected.
Open your database’s database’s main project manager window; get it displaying your tables.
Right-click on the table you want to add the records to.
Choose “Paste” from the pop-up menu.
A dialog will open (“Copy table”)
You want “APPEND DATA”, and you want to remove the tick in front of “Use first line as column names”.
Click the “Next” button.
On the second page, you’ll see two lists.
The first (“Source table”) indicates the columns in your spreadsheet, identified by the first value from each.
The second (“Destination table”) indicates the fields in the table you are appending the new records to.
Use the “up”/“down” arrows to “move” the field names of the table around, so that the top field in “destination” is where you want what’s in the top column-name in the “source”.
(For a simple just-add-new-records-with-suitable-values-for-primary-key task, as long as the table’s primary key is the first in the table, you won’t have to do any moving of things up or down.)
Don’t Worry when “nothing” happens when you do what’s next:
Click the “Create” button.
If “nothing” happens, be happy. Check your table. You should find the new records waiting there.
If you get an error message, you may have SOME of the records create. The error probably arose when the process met either a value that didn’t suit the data-type of the field you were trying to put it in, or you accidentally tried to create a record with an already-used value in the primary key field.
===
My environment: Windows 10 (2H22) / LO 7.1.1.2 / Embedded Firebird version? I don’t know how to find that!
