Unanswered: reference to a sequence in copied table structure
Hello to you all,
i've got the following problem :
I've got an table, say tab_a, that uses a sequence to populate the primary key field.
I copied the structure of this table with
'create table tab_b as select * from tab_a where 1=2;'
I disabled the primary key-constraint of tab_b and want to insert data independently of the original table tab_a, meaning, I don't want to have any data in the former primary key field.
Inserting data in the new tab_b (leaving out the former primary key) results in an automatic insert of the curval of the sequence associated with tab_a in the respective field of tab_b. this means that i get the same value (the actual value of the sequence) for each inserted row in the new table).
Tab_a and Tab_b belong to different users.
my question is :
How is it possible, that the new table tab_b refers to the sequence that was connected to tab_a and how can I disconnect tab_b from this sequence ?
Re: reference to a sequence in copied table structure
Originally posted by andrewst
That doesn't happen! If I followed you correctly, you did something like this:
SQL> create sequence seq_a;
SQL> create table tab_a( id integer, name varchar2(10) );
SQL> insert into tab_a values (seq_a.nextval, 'A' );
1 row created.
SQL> create table tab_b as select * from tab_a where 1=2;
SQL> insert into tab_b ( name ) select name from tab_a;
1 row created.
SQL> select * from tab_a;
SQL> select * from tab_b;
As you can see, the ID value is not set in tab_b because I didn't provide a value for it.
You're right, it cannot happen (this I was rather sure myself, but table-content told another language).... I found out that in the background a trigger was working filling the column with the unwanted values....
so, i tackled the wrong issue...
thanks for your help,