two tables with the same sequence

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

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.

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.

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?

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;