Recent

Author Topic: [Solved] Firebird 3 \ IBX : how to restart native auto-incremented IDs fields?  (Read 3772 times)

devEric69

  • Hero Member
  • *****
  • Posts: 558
Hello,

I have a firebird \ IBX (maybe) question: I've migrated a firebird 2.5 database towards 3.
In the process, I got rid of the SQL objects like generators, sequences, and triggers in the beforeInsert of each tables, i.e. i now use auto-incremented fields. It works well.

Now, as a result of my trials, I am using auto-incremented fields (ID) that are worth 45, 16, ... I would like to have a "clean" demo database, with only 3 records in each table, and that the next ID is 4 (for info., the SQL sequences + generators objects were allowed to be reinitialized, reset). So I've deleted all the records in order to leave only the first 3 records in each table. Unfortunately, when i ask to create new records, I have new IDs that are 46, 17, ... and not 4, 4, 4, ...

I've made a backup of the database with only 3 records in each table, then restored the database from this *.gbk: it doesn't change anything, i.e. the following IDs are still 47, 18, ...

So, does anyone know how to do this: reset auto-incremented IDs fields, or compress \ compact (as close to the "MS-Access way" - IIRC; i think so, but I'm not sure anymore - if possible) auto-incremented IDs fields, or remake a new database without records where each new record would have an ID=1 (at worst)? Which method is easier to use (with firebird tools), or to implement in Lazarus (with IBX)?

Regards.
« Last Edit: July 24, 2021, 07:47:08 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 827
I do not know how to set id values.
To create new clean tables, you can download DDL of all the definitions --- including custom defined generators, and run them --- not sure whether this would renew the auto-increment ids, but I guess it will.

devEric69

  • Hero Member
  • *****
  • Posts: 558
Yes, this's what i suspect: export everything (DDL + data) in SQL script, change the keys manually and recreate the database via the script. So there is nothing magical about it.

Otherwise, i display the key-IDs columns to be able to sort by group in the association tables (by foreign key groups). But as the "holes" of numbering bother me (this is just a "mania", without much interest), i will just add a functional option: the possibility of coloring the columns ID and their fonts in grey, so that they cannot be read explicitly.

« Last Edit: July 23, 2021, 03:47:35 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

dsiders

  • Hero Member
  • *****
  • Posts: 509
...
So, does anyone know how to do this: reset auto-incremented IDs fields, or compress \ compact (as close to the "MS-Access way", if possible) auto-incremented IDs fields, or remake a new database without records where each new record would have an ID=1 (at worst)? Which method is easier to use (with firebird tools), or to implement in Lazarus (with IBX)?

You can use DDL to change the identity columns:

Code: MySQL  [Select][+][-]
  1. ALTER TABLE ... ALTER COLUMN ... (RESTART WITH <n>)

It's up to you to make sure the new start value doesn't already exist in the table.

If you want a clean table, just re-create it and load the data again. It's not going to stay that way though, so obsessing over the "holes" is pointless.

If by compress / compact you mean changing the identity values for existing rows... that would be one more reason why I won't use MS Access. ;)

Lazarus 2.0.12, 2.2.0-RC1, 2.3.0 (Git) / FPC 3.2.0, 3.2.2 / x86-win64 / Windows 8.1

devEric69

  • Hero Member
  • *****
  • Posts: 558
Thanks for the tip @dsiders: i've internal knowledge gaps (DDL - SQL, inner services, ...) specific to Firebird.
:-[
« Last Edit: July 23, 2021, 04:57:58 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

devEric69

  • Hero Member
  • *****
  • Posts: 558
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

devEric69

  • Hero Member
  • *****
  • Posts: 558
nevertheless, this DDL statement ( "ALTER TABLE ... ALTER COLUMN ... (RESTART WITH <n>);" ) does nothing on the native IDentity fields, without external SQL generator. I will create an issue \ bug report at Firebird's gitHub, to see \ ask if this is normal.
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

devEric69

  • Hero Member
  • *****
  • Posts: 558
Well, finally, it works very well with Firebird's native auto-incremented fields. The DDL syntax has no parenthesis ( https://github.com/FirebirdSQL/firebird/issues/6906 ). For example, if there are only 3 records left in a very emptied table, and i want the next num#-ID to be 4 (more seriously, this can be useful to reset things at the beginning of the year, for example, like resetting the ID-counter of an INVOICES table to 1, ...):

Code: MySQL  [Select][+][-]
  1. ALTER TABLE MY_TABLE ALTER COLUMN MY_IDENTITY_COLUMN_ID RESTART WITH 3;
« Last Edit: July 23, 2021, 06:32:20 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 827
Quote
export everything (DDL + data) in SQL script, change the keys manually and recreate the database via the script.

I thought creating database first with DDL without data, and insert data without the key field. Will this work? It's not easy to move data from one database to another anyway.

 

TinyPortal © 2005-2018