Serial-like Int Column

I have an app where depending on the type of transaction being added or updated, the ticket number may or may not increment. I can't use a SERIAL datatype for ticket number because

Solution 1:

With the Informix DBMS, the SERIAL column will not change after it is inserted; indeed, you cannot update a SERIAL value at all. You can insert a new one with either 0 as the value - in which case a new value is generated - or you can insert some other value. If the other value already exists and there is a unique constraint, that will fail; if it does not exist, or if there is no unique constraint on the serial column, then it will succeed. If the value inserted is larger than the largest value previously inserted, then the next number to be inserted will be one larger again. If the number inserted is smaller, or negative, then there is no effect on the next number.

So, you could do your update without changing the value - no problem. If you need to change the number, you will have to do a delete and insert (or insert and delete), where the insert has a zero in it. If you prefer consistency and you use transactions, you could always delete, and then (re)insert the row with the same number or with a zero to trigger a new number. This assume you have a programming language running the SQL; I don't think you can tweak ISQL and Perform to do that automatically.

So, at this point, I don't see the problem on Informix.

With the appropriate version of IDS (anything that is supported), you can use SEQUENCE to control the values inserted too. This is based on the Oracle syntax and concept; DB2 also supports this. Other DBMS have other equivalent (but different) mechanisms for handling the auto-generated numbers.

Solution 2:

That's what sequences were created for and which is supported by most databases (MySQL being the only one that does not have sequences - not 100% sure about Informix though)

Any algorithm that relies on the SELECT MAX(id) anti-pattern is either dead-slow in a multi-user environment or will simply not work correctly in a multi-user environment.

If you need to support MySQL as well, I'd recommend to use the "native" "auto increment" type in each database (serial for PostgreSQL, auto_increment for MySQL, identity for SQL Server, sequence + trigger in Oracle and so on) and let the driver return the generated ID value

In JDBC there is a getGeneratedKeys() method and I'm sure other interfaces have something similar.

Solution 3:

From your tags it's hard to tell what database you are using.

For SQL Server (since it's listed) I suggest

ticket_num = (SELECTMAX(ticket_number) FROM transactions with (updlock)) +1