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

Martin's gravatar image

Martin
8.6k116149237
accept rate: 14%

Which version of ASA is in use ?

(13 Jul '11, 03:17) Thomas Dueme...

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

permanent link

answered 13 Jul '11, 03:36

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654
accept rate: 32%

edited 13 Jul '11, 04:48

Martin's gravatar image

Martin
8.6k116149237

Great, thanks Volker for the reference.

(13 Jul '11, 04:49) Martin

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.

permanent link

answered 20 Jul '11, 14:16

Costa's gravatar image

Costa
106118
accept rate: 0%

1

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...

(20 Jul '11, 16:54) Volker Barth

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.

(21 Jul '11, 12:18) Costa
Replies hidden

I really would like to accept more than 1 answer!

(22 Jul '11, 03:08) Martin
Replies hidden

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.

(22 Jul '11, 03:28) Volker Barth

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

(22 Jul '11, 03:30) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 13 Jul '11, 02:51

question was seen: 1,608 times

last updated: 22 Jul '11, 03:30