You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/09/08 01:03:50 UTC
svn commit: r441291 - /db/derby/docs/trunk/src/ref/rrefsqlj81859.dita
Author: bpendleton
Date: Thu Sep 7 16:03:50 2006
New Revision: 441291
URL: http://svn.apache.org/viewvc?view=rev&rev=441291
Log:
DERBY-1765: Update documentation for new ALTER TABLE features
This change adds information to the ALTER TABLE reference page in
the reference manual regarding the new ALTER COLUMN features:
- DERBY-119: ALTER COLUMN c [ NOT ] NULL
- DERBY-1491: ALTER COLUMN c [ WITH ] DEFAULT default-value
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?view=diff&rev=441291&r1=441290&r2=441291
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj81859.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj81859.dita Thu Sep 7 16:03:50 2006
@@ -38,6 +38,8 @@
<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>
+<li>change the nullability constraint for a column</li>
+<li>change the default value for a column</li>
</ul></p></section>
<section><title>Syntax</title><codeblock><b>ALTER TABLE <i><xref href="rreftablename.dita#rreftablename">table-Name</xref></i>
{
@@ -45,7 +47,7 @@
ADD <i><xref href="rrefsqlj13590.dita#rrefsqlj13590">CONSTRAINT clause</xref></i> |
DROP { PRIMARY KEY | FOREIGN KEY constraint-name | UNIQUE
constraint-name | CHECK constraint-name | CONSTRAINT constraint-name }
- ALTER <i><xref href="rrefsqlj81859.dita#rrefsqlj81859/rrefsqlj37860">column-alteration</xref></i> |
+ ALTER [ COLUMN ] <i><xref href="rrefsqlj81859.dita#rrefsqlj81859/rrefsqlj37860">column-alteration</xref></i> |
LOCKSIZE { ROW | TABLE }
}</b></codeblock></section>
<example id="rrefaltercolumndef"> <title>column-definition</title><codeblock><b><i><xref
@@ -54,7 +56,9 @@
[ [ 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 |
-<i>column-name</i> RESTART WITH integer-constant</b> </codeblock></example>
+<i>column-name</i> RESTART WITH integer-constant |
+<i>column-name</i> [ NOT ] NULL |
+<i>column-name</i> [ WITH ] DEFAULT default-value</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
@@ -133,6 +137,12 @@
the integer-constant. You must previously define the column with the IDENTITY
attribute (SQLSTATE 42837). If there are existing rows in the table, the values
in the column for which the SET INCREMENT default was added do not change.</p></li>
+<li>Modifying the nullability constraint of a column.
+ <p>You can add the NOT NULL constraint to an existing column. To do so
+ 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 or UNIQUE constraint.</p></li>
+<li>Changing the default value for a column.</li>
</ul></p></section>
<section><title>Setting defaults</title><p>You can specify a default value
for a new column. A default value is the value that is inserted into a column
@@ -192,7 +202,17 @@
<ph>-- increase the width of a VARCHAR column</ph>
ALTER TABLE SAMP.EMP_PHOTO ALTER PHOTO_FORMAT SET DATA TYPE VARCHAR(30);
<ph>-- change the lock granularity of a table</ph>
-ALTER TABLE SAMP.SALES LOCKSIZE TABLE;</b></codeblock></section>
+ALTER TABLE SAMP.SALES LOCKSIZE TABLE;
+
+<ph>-- Remove the NOT NULL constraint from the MANAGER column</ph>
+ALTER TABLE Employees ALTER COLUMN Manager NULL;
+<ph>-- Add the NOT NULL constraint to the SSN column</ph>
+ALTER TABLE Employees ALTER COLUMN ssn NOT NULL;
+
+<ph>-- Change the default value for the SALARY column</ph>
+ALTER TABLE Employees ALTER COLUMN Salary DEFAULT 1000.0
+
+</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
next execution. ALTER TABLE is not allowed if there are any open cursors that