Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    5

    Exclamation 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 ?

    thanks a lot to everybody,
    susanne

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: reference to a sequence in copied table structure

    That doesn't happen! If I followed you correctly, you did something like this:

    PHP Code:
    SQLcreate sequence seq_a;

    Sequence created.

    SQLcreate table tab_aid integername varchar2(10) );

    Table created.

    SQLinsert into tab_a values (seq_a.nextval'A' );

    1 row created.

    SQLcreate table tab_b as select from tab_a where 1=2;

    Table created.

    SQLinsert into tab_b name select name from tab_a;

    1 row created.

    SQLselect from tab_a;

            
    ID NAME
    ---------- ----------
             
    1 A

    SQL
    select from tab_b;

            
    ID NAME
    ---------- ----------
               

    As you can see, the ID value is not set in tab_b because I didn't provide a value for it.

  3. #3
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    5

    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:

    PHP Code:
    SQLcreate sequence seq_a;

    Sequence created.

    SQLcreate table tab_aid integername varchar2(10) );

    Table created.

    SQLinsert into tab_a values (seq_a.nextval'A' );

    1 row created.

    SQLcreate table tab_b as select from tab_a where 1=2;

    Table created.

    SQLinsert into tab_b name select name from tab_a;

    1 row created.

    SQLselect from tab_a;

            
    ID NAME
    ---------- ----------
             
    1 A

    SQL
    select from tab_b;

            
    ID NAME
    ---------- ----------
               

    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,
    susanne

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •