svn commit: r374145 - /db/derby/docs/trunk/src/ref/rrefsqlj81859.dita

[jt...@apache.org]

Author: jta
Date: Wed Feb  1 11:11:10 2006
New Revision: 374145

URL: http://svn.apache.org/viewcvs?rev=374145&view=rev
Log:
DERBY-869 Apply Eric Radzinski's patch that documents enhancement to
ALTER TABLE syntax that allows users to change next value to be generated
for an identity column.

Modified:
    db/derby/docs/trunk/src/ref/rrefsqlj81859.dita

Modified: db/derby/docs/trunk/src/ref/rrefsqlj81859.dita
URL: http://svn.apache.org/viewcvs/db/derby/docs/trunk/src/ref/rrefsqlj81859.dita?rev=374145&r1=374144&r2=374145&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj81859.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj81859.dita Wed Feb  1 11:11:10 2006
@@ -1,5 +1,5 @@
-<?xml version="1.0" encoding="utf-8"?>
-<!--Arbortext, Inc., 1988-2005, v.4002-->
+<?xml version="1.0" encoding="utf-8"?>
+
 <!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
  "../dtd/reference.dtd">
 <!-- 
@@ -36,6 +36,7 @@
 <li>drop an existing constraint from a table</li>
 <li>increase the width of a VARCHAR, CHAR VARYING, and CHARACTER VARYING column</li>
 <li>override row-level locking for the table (or drop the override)</li>
+<li>change the increment value and start value of the identity column</li>
 </ul></p></section>
 <section><title>Syntax</title><codeblock><b>ALTER TABLE <i><xref href="rreftablename.dita#rreftablename">table-Name</xref></i>
 {
@@ -51,28 +52,49 @@
 [ <i><xref href="rrefsqlj16095.dita#rrefsqlj16095">Column-level-constraint</xref></i> ]*
 [ [ WITH ] DEFAULT {<i>ConstantExpression</i> | NULL } ]</b></codeblock></example>
 <example id="rrefsqlj37860"><title>column-alteration</title><codeblock><b><i>column-Name</i> SET DATA TYPE VARCHAR(integer) |
-<i>column-name</i> SET INCREMENT BY integer-constant</b> </codeblock></example>
+<i>column-name</i> SET INCREMENT BY integer-constant |
+<i>column-name</i> RESTART WITH integer-constant</b> </codeblock></example>
 <section><p>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.</p><p>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.</p></section>
+the IDENTITY attribute.</p><p>RESTART WITH integer-constant specifies the
+next value to be generated for the identity column. RESTART WITH is useful
+for a table that has an identity column that was defined as GENERATED BY DEFAULT
+and that has a unique key defined on that identity column. Because GENERATED
+BY DEFAULT allows both manual inserts and system generated values, it is possible
+that manually inserted values can conflict with system generated values. To
+work around such conflicts, use the RESTART WITH syntax to specify the next
+value that will be generated for the identity column. Consider the following
+example, which involves a combination of automatically generated data and
+manually inserted data:<codeblock><b>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</b></codeblock></p><p>The system will automatically
+generate values for the identity column.  But now you need to manually insert
+some data into the identity column:<codeblock><b>INSERT INTO tauto VALUES (3,3)
+INSERT INTO tauto VALUES (4,4)
+INSERT INTO tauto VALUES (5,5)</b></codeblock></p><p>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:<codeblock><b>ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6</b></codeblock></p><p>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.</p></section>
 <section><title>Adding columns</title><p>The syntax for the <i><xref href="rrefsqlj81859.dita#rrefsqlj81859/rrefaltercolumndef">column-definition</xref
 ></i> for a new column is the same as for a column in a CREATE TABLE statement.
 This means that a column constraint can be placed on the new column within
 the ALTER TABLE ADD COLUMN statement. However, a column with a NOT NULL constraint
-can be added to an existing table if you give
-a default value; otherwise, an exception is thrown when the ALTER TABLE statement
-is executed. </p><p>Just as in CREATE TABLE, if the column definition includes
-a unique or primary key constraint, the column cannot contain null values,
-so the NOT NULL attribute must also be specified (SQLSTATE 42831).</p><note>If
-a table has an UPDATE trigger without an explicit column list, adding a column
-to that table in effect adds that column to the implicit update column list
-upon which the trigger is defined, and all references to transition variables
-are invalidated so that they pick up the new column.</note></section>
+can be added to an existing table if you give a default value; otherwise,
+an exception is thrown when the ALTER TABLE statement is executed. </p><p>Just
+as in CREATE TABLE, if the column definition includes a unique or primary
+key constraint, the column cannot contain null values, so the NOT NULL attribute
+must also be specified (SQLSTATE 42831).</p><note>If a table has an UPDATE
+trigger without an explicit column list, adding a column to that table in
+effect adds that column to the implicit update column list upon which the
+trigger is defined, and all references to transition variables are invalidated
+so that they pick up the new column.</note></section>
 <section><title>Adding constraints</title><p>ALTER TABLE ADD CONSTRAINT adds
 a table-level constraint to an existing table. Any supported table-level constraint
 type can be added via ALTER TABLE. The following limitations exist on adding