[Solved] Firebird 3 \ IBX : how to restart native auto-incremented IDs fields? lazarus / 63-Database ---------------------------------------------------------------------- devEric69 · 2021-07-23 10:52 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. ---------------------------------------------------------------------- egsuh · 2021-07-23 11:29 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 · 2021-07-23 13:32 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. ---------------------------------------------------------------------- dsiders · 2021-07-23 14:02 Quote from: devEric69 on July 23, 2021, 12:52:50 pm... 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE ... ALTER COLUMN ... (RESTART WITH ) 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. ---------------------------------------------------------------------- devEric69 · 2021-07-23 14:27 Thanks for the tip @dsiders: i've internal knowledge gaps (DDL - SQL, inner services, ...) specific to Firebird. ---------------------------------------------------------------------- devEric69 · 2021-07-23 14:59 For info., found on Firebird's site: https://www.firebirdsql.org/file/ documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-ddl- identity . ---------------------------------------------------------------------- devEric69 · 2021-07-23 15:17 nevertheless, this DDL statement ( "ALTER TABLE ... ALTER COLUMN ... (RESTART WITH );" ) 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. ---------------------------------------------------------------------- devEric69 · 2021-07-23 16:16 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ALTER TABLE MY_TABLE ALTER COLUMN MY_IDENTITY_COLUMN_ID RESTART WITH 3; ---------------------------------------------------------------------- egsuh · 2021-07-24 01:21 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.