2

Is it possible to have autoincrementing id among several tables?
What I mean exactly - I have (let's say five) tables, one of them is a table containing information about sales (sale_id, sold_item_id) and another four contain info about different kind of sold stuff. I want these four to share one pool of ids. How do I do that?

Edit.
I decided to choose Juxhin solution and I created additional table. Everytime I create a record in one of these 4 tables, I autoincrement new id in that additional table and this id is in one of columns of that new row.

spoko
  • 783
  • 1
  • 10
  • 24
  • 1
    Alot of other factors take place, you could just have a Data class and store current ID there and increment of that value – Juxhin Aug 18 '14 at 19:34

2 Answers2

1

This sounds like the use case for a sequence and the link seems to indicate that javadb supports this.

So you create one common sequence for all tables:

CREATE SEQUENCE MASEQUENCE

and then use it when inserting into your tables:

 INSERT INTO TAB1(ID,....) VALUES(NEXT VALUE FOR MYSEQUENCE,...)

Each NEXT VALUE will advance the sequence and so all ids will be unique across all tables.

piet.t
  • 11,718
  • 21
  • 43
  • 52
0

If you want a new record to be inserted into all 5 tables when you insert something into one of them, then you can create a trigger for this.

It may also be helpful to create foreign keys on the id columns in the other tables (to keep the tables in sync).

Dyre
  • 547
  • 1
  • 3
  • 2
  • I wanted the new record to be inserted into one table, but make sure that auto increment would give it an id from the common pool. Nevermind anymore, I picked Juxhin solution. – spoko Aug 20 '14 at 13:52