I have the following table in the database in postgreSQL
ThefactisthattheDescriptioncolumnisunique_keysobytestingquestionsIdecidedtoinsertsomerecordswiththerepeatedvaluesinthedescriptioncolumntocheckifitwasworkingwell,problemisthatevenwhentheintegrityerroroccursitdoesnotgivea" rollback " following the ID and continues counting, ie the 3rd record of my table I induced the error 6 times and anyway it keeps incrementing the ID when in fact it should go to 3, would anyone know how to fix it?
Sequence generation is / must be independent of the transaction. The idea is that it always maintains a unique, growing value: they are in sequence but are not guaranteed to be contiguous.
Imagine a scenario where you open a transaction, enter 1000 records, at the same time another bank user enters 100 records without any transaction. The ids generated inside and outside the transaction can not be the same, they must be unique. It would be impossible for the database to resolve this issue without stopping the insertions in other transactions and this would be disastrous for any type of database: it would detonate with performance.
If you try to implement this sequential by yourself, consider the same parallel access problem as a naive implementation will seem to work for both single and serial access scenarios, but it will tragically fail for parallel access scenarios, which are the most common scenarios for using a database.
Sequential is also determined before referential integrity is applied, since the full data of the line being entered must be available to perform this check, and the primary key of the line is required. For the same reasons of transaction independence, it is not possible to stop the other insertion routines until a referential integrity fault is determined (in this case neither referential integrity, but a single constraint index failure). Recall that indexes and " constraints" are not intrinsic data from a table, and can be removed and added without changing the structure. For the sake of performance, scheduling, and simplicity of implementation, the identities value is generated before actually inserting the line, and this occurs before the validation of the line, so it is not possible to "return" the value of the sequential a posteriori . For this reason, after the error, one or another identity will be skipped.
You do not have to worry about it in 99.999999% of the scenarios. Demanding that they are contiguous is, as a rule, unnecessary. It is also not good development practice if you rely on the logic of generating the identities of a database, as it may change with the version of the database. Use the identities as only the unique identifier of the record that they are. They can also serve to sort the data in the order of insertion in the bank (remember that this order can be mixed between multiple inserts and parallel transactions).
If you're worried about the wasted identities skipped you can stop worrying, it will never (or virtually never) come to harm you.
