Re: documenting DERBY -783: Enhancements to ALTER TABLE

[Eric Radzinski]

Thanks Mamta,

 

I will indeed wait to submit the patch.  In the meantime, though, here's my proposed change (in bold).  Please let me know if I've captured the important points (and sorry if the change seems long-winded - let me know if I've given too much info):

 

column-alteration

   column-name SET DATA TYPE VARCHAR(integer) |
   column-name SET INCREMENT BY integer-constant |
   column-name RESTART WITH integer-constant

In the column-alteration, SET INCREMENT BY integer-constant specifies
the interval between consecutive values of the identity column. The next value
to be generated for the identity column will be determined from the last assigned
value with the increment applied. The column must already be defined with
the IDENTITY attribute.

 

RESTART WITH integer-constan t specifies the next value to be generated for the identity column, which is useful for tables that have an identity column that was defined as GENERATED BY DEFAULT and that was defined a unique key, but that you might need to manually insert data into. Consider the following example that involves a combination of automatically generated data and manually inserted data:

 

   autocommit on;
   CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT);
   CREATE UNIQUE INDEX tautoInd ON tauto(i);
   INSERT INTO tauto(k) values 1,2; 

 

The system will automatically generate values for the identity column.  But
now you need to manually insert some data into the identity column:

 

  INSERT INTO tauto VALUES (3,3);
  INSERT INTO tauto VALUES (4,4);
  INSERT INTO tauto VALUES (5,5);

 

The identity column has used values 1 through 5 at this point.  If you now want
the system to generate a value, the system will generate a 3, which will result
in a unique key exception because the value 3 has already been manually inserted.  To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6.

 

ALTER TABLE does not affect any view that references
the table being altered. This includes views that have an "*" in their SELECT
list. You must drop and re-create those views if you wish them to return the
new columns.

Mamta Satoor <[EMAIL PROTECTED]> wrote:

Also, Eric, my change for ALTER TABLE ... RESTART WITH are not checked into the codeline yet (there is a review package pending on the list). I think it will be better if you wait before  submitting a patch for doc changes because it will be good to keep doc in sync with the codeline rather than letting it get ahead of it.

 

Just a thought,

Mamta

 

On 1/18/06, Mamta Satoor <[EMAIL PROTECTED]> wrote:

Actually, the new syntax would leave the ALTER TABLE statement as it is in the documentation. What would change is the definition of column-alteration. It would look as follows

column-Name SET DATA TYPE VARCHAR(integer) |  column-name SET INCREMENT BY integer-constant |column-name RESTART WITH integer-constant   
And then include information on what this RESTART WITH syntax means for a generated column in the reference guide like you mentioned.
Thanks for looking into documenting this,  Mamta

On 1/18/06, Eric Radzinski <[EMAIL PROTECTED] > wrote:

Can y ou please confirm or correct my assumption about what is needed to document the enhancements to ALTER TABLE syntax.

 

Is the only change to update the ALTER TABLE syntax in the Reference Guide to include RESTART WITH integer-constant:

 

ALTER TABLE table-Name

{

    ADD COLUMN column-definition |

    ADD CONSTRAINT clause |

    DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE

         constraint-name | CHECK constraint-name | CONSTRAINT constraing-name }

   ALTER column-alteration

   LOCKSIZE { ROW | TABLE }

   RESTART WITH integer-constant

}

 

and then to document the behavior of  RESTART W ITH integer-constant with in this same reference topic.    Anything else???

---

Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.

---

Yahoo! Photos – Showcase holiday pictures in hardcover
Photo Books. You design it and we’ll bind it!