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

[rh...@apache.org]

Author: rhillegas
Date: Sun Apr 10 23:24:28 2016
New Revision: 1738488

URL: http://svn.apache.org/viewvc?rev=1738488&view=rev
Log:
DERBY-6883: Document the SET GENERATED clause of the ALTER TABLE statement.

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

Modified: db/derby/docs/trunk/src/ref/rrefsqlj81859.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj81859.dita?rev=1738488&r1=1738487&r2=1738488&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj81859.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj81859.dita Sun Apr 10 23:24:28 2016
@@ -41,6 +41,7 @@ tables</indexterm><indexterm>Tables<inde
 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>
+<li>Change an identity column from ALWAYS to DEFAULT BY behavior or vice-versa</li>
 <li>Change the nullability constraint for a column</li>
 <li>Change the default value for a column</li>
 </ul></p></section>
@@ -69,6 +70,7 @@ href="rrefconstraintname.dita#rrefconstr
 <i>columnName</i> SET DATA TYPE VARCHAR( <i>integer</i> ) FOR BIT DATA |
 <i>columnName</i> SET INCREMENT BY <i>integerConstant</i> |
 <i>columnName</i> RESTART WITH <i>integerConstant</i> |
+<i>columnName</i> SET GENERATED { ALWAYS | BY DEFAULT } |
 <i>columnName</i> { SET | DROP } NOT NULL |
 <i>columnName</i> [ NOT ] NULL |
 <i>columnName</i> [ WITH | SET ] DEFAULT <i>defaultValue</i> |
@@ -97,7 +99,14 @@ used values 1 through 5 at this point. I
 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
+with RESTART WITH 6:<codeblock><b>ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6</b></codeblock></p>
+
+<p>
+SET GENERATED ALWAYS causes Derby to not accept an overriding value for an identity column when a row is inserted or updated.
+SET GENERATED BY DEFAULT causes Derby to permit these overrides.
+</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>
@@ -191,6 +200,7 @@ the column name.</p><p>You are not allow
 the data type. You are not allowed to increase the width of a column that
 is part of a primary or unique key referenced by a foreign key constraint
 or that is part of a foreign key constraint.</p></li>
+
 <li>Specifying the interval between consecutive values of the identity column.<p>To
 set an interval between consecutive values of the identity column, specify
 the <i>integerConstant</i>. You must previously define the column with the IDENTITY
@@ -201,6 +211,13 @@ in the column for which the SET INCREMEN
         there must not be existing NULL values for the column in the table.</p>
     <p>You can remove the NOT NULL constraint from an existing column. To do
         so the column must not be used in a PRIMARY KEY constraint.</p></li>
+
+<li>Changing an identity column from GENERATED ALWAYS to GENERATED BY DEFAULT behavior or vice-versa.
+<p>
+ The SET GENERATED clause may only be applied to identity columns. It cannot be used to convert a non-identity column into an identity column. This clause can be useful if you need to preserve key values when bulk-loading a table from a snapshot or exported dump.
+</p>
+</li>
+
 <li>Changing the default value for a column.
 <p>You can use DEFAULT <i>default-value</i> to change a column default. To disable a
 previously set default, use DROP DEFAULT (alternatively, you can specify NULL as
@@ -279,6 +296,15 @@ ALTER TABLE Employees ALTER COLUMN ssn N
 <ph>-- Change the default value for the SALARY column</ph>
 ALTER TABLE Employees ALTER COLUMN Salary DEFAULT 1000.0
 ALTER TABLE Employees ALTER COLUMN Salary DROP DEFAULT
+
+<ph>-- Bulk load a table by temporarily changing a GENERATED ALWAYS identity column
+-- into a GENERATED BY default column.
+-- After loading the table, reset the identity column to be GENERATED ALWAYS
+-- and move its sequence number forward past the last inserted key.</ph>
+ALTER TABLE targetTable ALTER COLUMN keyCol SET GENERATED BY DEFAULT;
+INSERT INTO targetTable SELECT * FROM sourceTable;
+ALTER TABLE targetTable ALTER COLUMN keyCol SET GENERATED ALWAYS;
+ALTER TABLE targetTable ALTER COLUMN keyCol RESTART WITH 1234567;
 </b></codeblock></section>
 <section><title>Results</title><p>An ALTER TABLE statement causes all statements
 that are dependent on the table being altered to be recompiled before their