1

Is possible to have two tables with the same incrementing sequence?

I was trying to do a tree with ID, NAME, ParentID and i have to join two tables. If i have different id the tree scheme of ID - ParentId will not work.

     Table A                Table B
  ID | NAME | PID       ID | NAME | PID
  1  | xpto | 0          1 | xpto | 1
APC
  • 144,005
  • 19
  • 170
  • 281
macwadu
  • 907
  • 4
  • 24
  • 44

4 Answers4

6

If you are doing both inserts at the same time, you can use SEQUENCE.NEXTVAL for the insert into the first table to get a new ID, and then SEQUENCE.CURRVAL for the insert into the second table to reuse the same ID.

Craig
  • 5,740
  • 21
  • 30
5

I found the answer: "Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables."

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm

Tanks for your help.

macwadu
  • 907
  • 4
  • 24
  • 44
  • This is kind of what Craig said. I gave him a +1. – paparazzo Sep 02 '11 at 22:03
  • This is correct - the documentation usually is :) - and answers the question you pose in the first paragraph. But it isn't clear how this resolves the problem you have with your two tables. – APC Sep 03 '11 at 02:12
3

You could have a master table that is nothing but the sequence PK/FK and then have two child tables. Insert a row in the master just to get the sequence and then use that sequence as the PK in the child tables. If the child tables have the same sequence then why is not one table?

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0
create sequence v_seq
INCREMENT by 1
minvalue 1
maxvalue 10;

Sample Image

create table v_t_s_emp(v_id number,vname varchar2(10));
insert into v_t_s_emp values(v_seq.nextval,'krishna');
create table v_t_s_emp1(v_id number,vname varchar2(10));
insert into v_t_s_emp1 values(v_seq.nextval,'RAMesh');
commit;


select * from v_t_s_emp
union all
select * from v_t_s_emp1;
abhinav3414
  • 946
  • 3
  • 9
  • 31
Mohan Reddy
  • 19
  • 4