db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1740436 - in /db/derby/docs/branches/10.12: ./ src/ref/rrefsqlj81859.dita
Date Fri, 22 Apr 2016 01:09:45 GMT
Author: rhillegas
Date: Fri Apr 22 01:09:45 2016
New Revision: 1740436

URL: http://svn.apache.org/viewvc?rev=1740436&view=rev
Log:
DERBY-6883: Port revision 1738488 (derby-6883-01-ab-refManualChanges.diff) from trunk docs
to 10.12 docs.

Modified:
    db/derby/docs/branches/10.12/   (props changed)
    db/derby/docs/branches/10.12/src/ref/rrefsqlj81859.dita

Propchange: db/derby/docs/branches/10.12/
------------------------------------------------------------------------------
    svn:mergeinfo = /db/derby/docs/trunk:1738488

Modified: db/derby/docs/branches/10.12/src/ref/rrefsqlj81859.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/branches/10.12/src/ref/rrefsqlj81859.dita?rev=1740436&r1=1740435&r2=1740436&view=diff
==============================================================================
--- db/derby/docs/branches/10.12/src/ref/rrefsqlj81859.dita (original)
+++ db/derby/docs/branches/10.12/src/ref/rrefsqlj81859.dita Fri Apr 22 01:09:45 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



Mime
View raw message