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?
asked 13 Jul '11, 02:51
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.
answered 20 Jul '11, 14:16