How to share an autoincrement between two tables?

[MCMartin]

Is it possible to have an autoincrement whose values are shared between two tables? So, table A starts with a field at 1, next a row is inserted in B shall be 2, next row in A would be then 3?

[thomas_duemesnil]

Which version of ASA is in use ?

[VolkerBarth · Accepted solution]

Yes, starting with SA 12, you can use SEQUENCES for this.

[MCMartin · Accepted solution]

Great, thanks Volker for the reference.

[Former Member]

A solution for SA 11 :

1) Create a DUMMY Table with a NOT NULL UNSIGNED (BIG)INT DEFAULT AUTOINCREMENT column.

2) To get the next key use the Get_Identity() function with the name of the DUMMY table as first parameter.

3) The value of the increment is visible in the max_identity column from the SYSTABCOL system view.

If you have to unload / reload the database be carreful to reset the value with the good number. You can use the sa_reset_identity() procedure to do this job.

[VolkerBarth]

This is an interesting solution - so to simulate a "default shared autoincrement", one might create a before insert trigger for those tables that are to share the same autoincrement values, and in these triggers call get_identity() on the dummy table to reserve and store the next value.

I haven't tested this, but as get_identity() was introduced in 8.0.1, it should work in this version and above...

[Former Member]

We use this solution since 3 years with 2 SA 9 databases and a syntax very similar to Sequence.

We don't use before insert trigger...

If we need to know the key after the insert, we write a function / procedure that first call get_identity(), second execute the insert statement and third return the key if success and 0 in other case.

If we don't need to know the key we embed the get_identity() call directly in the insert statement.

[MCMartin]

I really would like to accept more than 1 answer!

[VolkerBarth]

Well, I had suggested the trigger based approach as an alternative for a DEFAULT value - so you would just omit the column when inserting rows (as one usually would do with a DEFAULT AUTOINCREMENT). Then users of this table would not need to make the get_identity() call (and might even not need to know about this "hand-made sequence" solution).

But if there's no need to "hide" this approach, then your way to use get_identity() is more obvious and possibly easier:)

For the LOAD/UNLOAD problem, one could also use a DatabaseStart event to select the maximum value of the "shared" columns and insert this into the dummy table. That should retain the maximum value when unloading the database and will omit the need for the sa_reset_identity() call.

[VolkerBarth]

I agree - feel free to make a SQLA suggestion:)