[H2 and Firebird]auto_increment, identity or trigger plus generator?

Question

Hi all, in Firebird the only way to have an auto increment coloumn is to setup a generator and use it with a trigger.

In H2 db there are auto_increment and identity keywords to do it.

What is the best way?

Mysql use auto_increment too, but there is some problems with cuncurrence read and table lock, isn't true?

Thank you.

Answer 1

No, there's no problem of concurrent read and table lock.

The advantage of using the pseudokey feature native to each RDBMS is that the RDBMS engine handles allocation of id values atomically, so concurrent clients will not allocate the same id value. It only needs a brief lock on the internal id counter, not any whole table.

What is the best way is immaterial. You should use the feature provided by the RDBMS you're using. Unfortunately, pseudokeys weren't defined in the ANSI SQL standard until SQL:2003. By that time, each vendor had created their own proprietary feature and syntax.

• Oracle uses SEQUENCE objects which are sort of like Firebird GENERATOR or SEQUENCE objects.
• Microsoft SQL Server uses IDENTITY as a column option.
• IBM DB2 and PostgreSQL both support sequences, but they also have some declarative magic to make columns take their values from sequences implicitly.
• MySQL uses the AUTO_INCREMENT column option, and also supports a pseudo data type SERIAL to give it some cross-compatibility with PostgreSQL.
• SQLite just assumes any integer primary key column is auto-incrementing.

---

If H2 supports both IDENTITY and AUTO_INCREMENT, it's probably to make it more familiar for people migrating from either MySQL or Microsoft SQL Server. I don't know H2, but I would guess both syntax forms access the same internal feature.

I found the docs for H2. It appears both IDENTITY and AUTO_INCREMENT make use of a SEQUENCE.