Forum > Database
[Solved] Firebird 3 \ IBX : how to restart native auto-incremented IDs fields?
devEric69:
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:
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:
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:
--- 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)?
--- End quote ---
You can use DDL to change the identity columns:
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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. ;)
devEric69:
Thanks for the tip @dsiders: i've internal knowledge gaps (DDL - SQL, inner services, ...) specific to Firebird.
:-[
Navigation
[0] Message Index
[#] Next page