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 jt...@apache.org on 2005/06/24 00:48:23 UTC
svn commit: r201514 - in /incubator/derby/docs/trunk/src/ref:
rrefsqlj24513.dita rrefsqlj30540.dita rrefsqlj37836.dita
Author: jta
Date: Thu Jun 23 15:48:22 2005
New Revision: 201514
URL: http://svn.apache.org/viewcvs?rev=201514&view=rev
Log:
DERBY-275 Committed Jeff Levitt's patch that documents the BY DEFAULT option.
Modified:
incubator/derby/docs/trunk/src/ref/rrefsqlj24513.dita
incubator/derby/docs/trunk/src/ref/rrefsqlj30540.dita
incubator/derby/docs/trunk/src/ref/rrefsqlj37836.dita
Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj24513.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj24513.dita?rev=201514&r1=201513&r2=201514&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj24513.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj24513.dita Thu Jun 23 15:48:22 2005
@@ -1,4 +1,6 @@
<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
<!--
Copyright 1997, 2004 The Apache Software Foundation or its licensors, as applicable.
@@ -14,123 +16,30 @@
See the License for the specific language governing permissions and
limitations under the License.
-->
-
-<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
- "../dtd/reference.dtd">
<reference id="rrefsqlj24513" xml:lang="en-us">
<title>CREATE TABLE statement</title>
<prolog><metadata>
-<keywords><indexterm>CREATE TABLE statement</indexterm></keywords>
+<keywords><indexterm>CREATE TABLE statement</indexterm><indexterm>Defaults</indexterm>
+<indexterm>Column defaults</indexterm></keywords>
</metadata></prolog>
<refbody>
<section> <p>A CREATE TABLE statement creates a table. Tables contain columns
and constraints, rules to which data must conform. Table-level constraints
specify a column or columns. Columns have a data type and can specify column
-constraints (column-level constraints).</p> <p>For information about constraints,
-see <xref href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</p> <p>You can
-specify a default value for a column. A default value is the value to be inserted
+constraints (column-level constraints).</p><p>For information about constraints,
+see <xref href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</p><p>You can specify
+a default value for a column. A default value is the value to be inserted
into a column if no other value is specified. If not explicitly specified,
-the default value of a column is NULL. See <xref href="rrefsqlj24513.dita#rrefsqlj24513/sqlj64478"></xref>.</p> <p>You
+the default value of a column is NULL. See <xref href="rrefsqlj30540.dita#rrefsqlj30540/sqlj64478"></xref>.</p><p>You
can specify storage properties such as page size for a table by calling the <codeph>SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY</codeph> system
-procedure.</p> <p>If a qualified table name is specified, the schema name
-cannot begin with <i>SYS</i>.</p></section>
-<refsyn><title>Syntax</title> <codeblock><b>CREATE TABLE <i><xref href="rreftablename.dita#rreftablename"></xref></i>
- ( {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540"></xref></i> | <i><xref href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i>}
- [ , {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540"></xref></i> | <i><xref href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i>} ] * )</b></codeblock> <p>The
-syntax of <i>Data-Type</i> is described in <xref href="crefsqlj31068.dita#crefsqlj31068"></xref>.</p> <p>The
-syntaxes of <i><xref href="rrefsqlj16095.dita#rrefsqlj16095"></xref></i> and <i><xref
-href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i> are described in <xref href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</p> <p>The
-IDENTITY keyword can only be specified if the data type associated with the
-column is one of the following exact integer types. <ul>
-<li>SMALLINT</li>
-<li>INT</li>
-<li>BIGINT</li>
-</ul></p> </refsyn>
-<section id="sqlj64478"><title>Column Default</title> <p><indexterm>Defaults</indexterm><indexterm>Column
-defaults</indexterm>For the definition of a default value, a <i>ConstantExpression</i> is
-an expression that does not refer to any table. It can include constants,
-date-time special registers, current schemas, users, and null.</p> </section>
-<section id="sqlj69411"><title>Identity column attributes</title> <p><indexterm>Identity
-Column Attributes</indexterm>For SMALLINT, INT, and BIGINT columns with identity
-attributes, <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
-assigns increasing integer values to the column. Identity column attributes
-behave like other defaults in that when an insert statement does not specify
-a value for the column, <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
-provides the value. However, the value is not a constant; <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
-increments the default value every time a row is inserted. Also, unlike other
-defaults, you are not allowed to insert a value directly into or update an
-identity column. </p> <p>By default, the initial value of an identity column
-is 1, and the amount of the increment is 1. You can specify non-default values
-for both the initial value and the interval amount when you define the column
-with the key words START WITH and INCREMENT BY. And if you specify a negative
-number for the increment value, <ph conref="refconrefs.dita#prod/productshortname"></ph> <i>decrements</i> the
-value with each insert. If this value is 0, or positive, <ph conref="refconrefs.dita#prod/productshortname"></ph> increments
-the value with each insert.</p> <p>The maximum and minimum values allowed
-in identity columns are determined by the data type of the column. Attempting
-to insert a value outside the range of values supported by the data type raises
-an exception. <table frame="all"><title>Maximum and Minimum Values for Columns
-with Generated Column Specs</title>
-<tgroup cols="3" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="16*"/>
-<colspec colname="2" colnum="2" colwidth="42*"/><colspec colname="3" colnum="3"
-colwidth="42*"/>
-<thead>
-<row>
-<entry align="left" colname="1" valign="bottom">Data type</entry>
-<entry align="left" colname="2" valign="bottom">Maximum Value</entry>
-<entry align="left" colname="3" valign="bottom">Minimum Value</entry>
-</row>
-</thead>
-<tbody>
-<row>
-<entry colname="1">SMALLINT</entry>
-<entry colname="2">32767 (<i>java.lang.Short.MAX_VALUE</i>)</entry>
-<entry colname="3">-32768 (<i>java.lang.Short.MIN_VALUE</i>)</entry>
-</row>
-<row>
-<entry colname="1">INT</entry>
-<entry colname="2">2147483647 (<i>java.lang.Integer.MAX_VALUE</i>)</entry>
-<entry colname="3">-2147483648 (<i>java.lang.Integer.MIN_VALUE</i>)</entry>
-</row>
-<row>
-<entry colname="1">BIGINT</entry>
-<entry colname="2">9223372036854775807 (<i>java.lang.Long.MAX_VALUE</i>)</entry>
-<entry colname="3">-9223372036854775808 (<i>java.lang.Long.MIN_VALUE</i>)</entry>
-</row>
-</tbody>
-</tgroup>
-</table></p> <p>Automatically generated values in an identity column are unique.
-Use a primary key or unique constraint on a column to guarantee uniqueness.
-Creating an identity column <i>does not</i> create an index on the column.</p> <p>The <codeph>IDENTITY_VAL_LOCAL</codeph> function
-is a non-deterministic function that returns the most recently assigned value
-for an identity column. See <xref href="rrefidentityvallocal.dita#rrefidentityvallocal"></xref> for
-more information.</p> <note>Specify the schema, table, and column name using
-the same case as those names are stored in the system tables--that is, all
-upper case unless you used delimited identifiers when creating those database
-objects.</note> <p><ph conref="refconrefs.dita#prod/productshortname"></ph> keeps
-track of the last increment value for a column in a cache. It also stores
-the value of what the next increment value will be for the column on disk
-in the <i>AUTOINCREMENTVALUE</i> column of the <i>SYS.SYSCOLUMNS</i> system
-table. Rolling back a transaction does not undo this value, and thus rolled-back
-transactions can leave "gaps" in the values automatically inserted into an
-identity column. <ph conref="refconrefs.dita#prod/productshortname"></ph> behaves
-this way to avoid locking a row in <i>SYS.SYSCOLUMNS</i> for the duration
-of a transaction and keeping concurrency high. </p> <p><indexterm>Identity
-columns <indexterm>and triggers</indexterm></indexterm>When an insert happens
-within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement
-into the identity column is available from <i>ConnectionInfo</i> only within
-the trigger code. The trigger code is also able to see the value inserted
-by the statement that caused the trigger to fire. However, the statement that
-caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement
-into the identity column. Likewise, triggers can be nested (or recursive).
-An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL
-statement that causes trigger T2 to fire. If both T1 and T2 insert rows into
-a table that cause <ph conref="refconrefs.dita#prod/productshortname"></ph> to
-insert into an identity column, trigger T1 cannot see the value caused by
-T2's insert, but T2 can see the value caused by T1's insert. Each nesting
-level can see increment values generated by itself and previous nesting levels,
-all the way to the top-level SQL statement that initiated the recursive triggers.
-You can only have 16 levels of trigger recursion.</p> </section>
-<example><codeblock><b>CREATE TABLE HOTELAVAILABILITY
+procedure.</p><p>If a qualified table name is specified, the schema name cannot
+begin with <i>SYS</i>.</p></section>
+<refsyn><title>Syntax</title><codeblock><b>CREATE TABLE <i><xref href="rreftablename.dita#rreftablename"></xref></i>
+ ( {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540"></xref></i> | <i><xref
+href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i>}
+ [ , {<i><xref href="rrefsqlj30540.dita#rrefsqlj30540"></xref></i> | <i><xref
+href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i>} ] * )</b></codeblock> </refsyn>
+<example><title>Example</title><codeblock><b>CREATE TABLE HOTELAVAILABILITY
(HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
<ph>-- the table-level primary key definition allows you to
@@ -147,9 +56,8 @@
-- of 5.</ph>
CREATE TABLE GROUPS
(GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
- (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));</b></codeblock> <note>For
+ (START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));</b></codeblock><note>For
more examples of CREATE TABLE statements using the various constraints, see <xref
href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</note> </example>
</refbody>
</reference>
-
Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj30540.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj30540.dita?rev=201514&r1=201513&r2=201514&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj30540.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj30540.dita Thu Jun 23 15:48:22 2005
@@ -1,4 +1,6 @@
<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
<!--
Copyright 1997, 2004 The Apache Software Foundation or its licensors, as applicable.
@@ -14,17 +16,22 @@
See the License for the specific language governing permissions and
limitations under the License.
-->
-
-<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
- "../dtd/reference.dtd">
<reference id="rrefsqlj30540" xml:lang="en-us">
<title>column-definition</title>
<refbody>
<example> <codeblock><b><i><xref href="rrefsimplecolumnname.dita#rrefsimplecolumnname"></xref></i> <i>DataType</i>
[ <i><xref href="rrefsqlj16095.dita#rrefsqlj16095"></xref></i> ]*
- [ <i><xref href="rrefsqlj37836.dita#rrefsqlj37836"></xref></i> ]
+ [ [ WITH ] DEFAULT { ConstantExpression | NULL }
+ |<i><xref href="rrefsqlj37836.dita#rrefsqlj37836"></xref></i> ]
[ <i><xref href="rrefsqlj16095.dita#rrefsqlj16095"></xref></i> ]*
</b></codeblock></example>
+<section><p>The syntax of <i>Data-Type</i> is described in <xref href="crefsqlj31068.dita#crefsqlj31068"></xref>.</p><p>The
+syntaxes of <i><xref href="rrefsqlj16095.dita#rrefsqlj16095"></xref></i> and <i><xref
+href="rrefsqlj42154.dita#rrefsqlj42154"></xref></i> are described in <xref
+href="rrefsqlj13590.dita#rrefsqlj13590"></xref>.</p></section>
+<section id="sqlj64478"><title>Column default</title><p>For the definition
+of a default value, a <i>ConstantExpression</i> is an expression that does
+not refer to any table. It can include constants, date-time special registers,
+current schemas, users, and null.</p> </section>
</refbody>
</reference>
-
Modified: incubator/derby/docs/trunk/src/ref/rrefsqlj37836.dita
URL: http://svn.apache.org/viewcvs/incubator/derby/docs/trunk/src/ref/rrefsqlj37836.dita?rev=201514&r1=201513&r2=201514&view=diff
==============================================================================
--- incubator/derby/docs/trunk/src/ref/rrefsqlj37836.dita (original)
+++ incubator/derby/docs/trunk/src/ref/rrefsqlj37836.dita Thu Jun 23 15:48:22 2005
@@ -1,4 +1,6 @@
<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
<!--
Copyright 1997, 2004 The Apache Software Foundation or its licensors, as applicable.
@@ -14,18 +16,138 @@
See the License for the specific language governing permissions and
limitations under the License.
-->
-
-<!DOCTYPE reference PUBLIC "-//IBM//DTD DITA Reference//EN"
- "../dtd/reference.dtd">
<reference id="rrefsqlj37836" xml:lang="en-us">
<title>generated-column-spec</title>
+<prolog><metadata>
+<keywords><indexterm>Identity column attributes</indexterm><indexterm>generated-column-spec</indexterm>
+</keywords>
+</metadata></prolog>
<refbody>
-<example> <codeblock><b>[ [ WITH ] DEFAULT {<i>ConstantExpression</i> | NULL }
-|
-[ GENERATED ALWAYS AS IDENTITY
+<example> <codeblock><b>[ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH IntegerConstant
[ ,INCREMENT BY IntegerConstant] ) ] ] ]
</b></codeblock></example>
+<section id="sqlj69411"><title>Identity column attributes</title><p>For SMALLINT,
+INT, and BIGINT columns with identity attributes, <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
+assigns increasing integer values to the column. Identity column attributes
+behave like other defaults in that when an insert statement does not specify
+a value for the column, <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
+provides the value. However, the value is not a constant; <ph conref="refconrefs.dita#prod/productshortname"></ph> automatically
+increments the default value at insertion time.</p><p>The IDENTITY keyword
+can only be specified if the data type associated with the column is one of
+the following exact integer types. <ul>
+<li>SMALLINT</li>
+<li>INT</li>
+<li>BIGINT</li>
+</ul></p><p>There are two kinds of identity columns in <ph conref="refconrefs.dita#prod/productshortname"></ph>:
+those which are GENERATED ALWAYS and those which are GENERATED BY DEFAULT. <dl>
+<dlentry>
+<dt>GENERATED ALWAYS</dt>
+<dd>An identity column that is GENERATED ALWAYS will increment the default
+value on every insertion and will store the incremented value into the column.
+Unlike other defaults, you cannot insert a value directly into or update an
+ identity column that is GENERATED ALWAYS. Instead, either specify the DEFAULT
+keyword when inserting into the identity column, or leave the identity column
+out of the insertion column list altogether. For example:<codeblock>create table greetings
+ (i int generated always as identity, ch char(50));
+insert into greetings values (DEFAULT, 'hello');
+insert into greetings(ch) values ('bonjour');</codeblock>Automatically generated
+values in a GENERATED ALWAYS identity column are unique. Creating an identity
+column does not create an index on the column.</dd>
+</dlentry><dlentry>
+<dt>GENERATED BY DEFAULT</dt>
+<dd><p>An identity column that is GENERATED BY DEFAULT will only increment
+and use the default value on insertions when no explicit value is given. Unlike
+GENERATED ALWAYS columns, you can specify a particular value in an insertion
+statement to be used instead of the generated default value.</p><p>To use
+the generated default, either specify the DEFAULT keyword when inserting into
+the identity column, or just leave the identity column out of the insertion
+column list. To specify a value, included it in the insertion statement. For
+example:<codeblock>create table greetings
+ (i int generated by default as identity, ch char(50));
+<b>-- specify value "1":</b>
+insert into greetings values (1, 'hi');
+<b>-- use generated default</b>
+insert into greetings values (DEFAULT, 'salut');
+<b>-- use generated default</b>
+insert into greetings(ch) values ('bonjour'); </codeblock>Note that unlike
+a GENERATED ALWAYS column, a GENERATED BY DEFAULT column does not guarantee
+uniqueness. Thus, in the above example, the <codeph>hi</codeph> and <codeph>salut</codeph> rows
+will both have an identity value of "1", because the generated column starts
+at "1" and the user-specified value was also "1". To prevent this, you can
+use the "STARTS WITH" keyword described below. To check for this condition
+and disallow it, you can use a primary key or unique constraint on the GENERATED
+BY DEFAULT identity column.</p></dd>
+</dlentry></dl></p><p>By default, the initial value of an identity column
+is 1, and the amount of the increment is 1. You can specify non-default values
+for both the initial value and the interval amount when you define the column
+with the key words STARTS WITH and INCREMENT BY. And if you specify a negative
+number for the increment value, <ph conref="refconrefs.dita#prod/productshortname"></ph> <i>decrements</i> the
+value with each insert. If this value is 0, or positive, <ph conref="refconrefs.dita#prod/productshortname"></ph> increments
+the value with each insert.</p><p>The maximum and minimum values allowed in
+identity columns are determined by the data type of the column. Attempting
+to insert a value outside the range of values supported by the data type raises
+an exception. <table frame="all"><title>Maximum and Minimum Values for Columns
+with Generated Column Specs</title>
+<tgroup cols="3" colsep="1" rowsep="1"><colspec colname="1" colnum="1" colwidth="16*"/>
+<colspec colname="2" colnum="2" colwidth="42*"/><colspec colname="3" colnum="3"
+colwidth="42*"/>
+<thead>
+<row>
+<entry align="left" colname="1" valign="bottom">Data type</entry>
+<entry align="left" colname="2" valign="bottom">Maximum Value</entry>
+<entry align="left" colname="3" valign="bottom">Minimum Value</entry>
+</row>
+</thead>
+<tbody>
+<row>
+<entry colname="1">SMALLINT</entry>
+<entry colname="2">32767 (<i>java.lang.Short.MAX_VALUE</i>)</entry>
+<entry colname="3">-32768 (<i>java.lang.Short.MIN_VALUE</i>)</entry>
+</row>
+<row>
+<entry colname="1">INT</entry>
+<entry colname="2">2147483647 (<i>java.lang.Integer.MAX_VALUE</i>)</entry>
+<entry colname="3">-2147483648 (<i>java.lang.Integer.MIN_VALUE</i>)</entry>
+</row>
+<row>
+<entry colname="1">BIGINT</entry>
+<entry colname="2">9223372036854775807 (<i>java.lang.Long.MAX_VALUE</i>)</entry>
+<entry colname="3">-9223372036854775808 (<i>java.lang.Long.MIN_VALUE</i>)</entry>
+</row>
+</tbody>
+</tgroup>
+</table></p><p>Automatically generated values in an identity column are unique.
+Use a primary key or unique constraint on a column to guarantee uniqueness.
+Creating an identity column <i>does not</i> create an index on the column.</p><p>The <codeph>IDENTITY_VAL_LOCAL</codeph> function
+is a non-deterministic function that returns the most recently assigned value
+for an identity column. See <xref href="rrefidentityvallocal.dita#rrefidentityvallocal"></xref> for
+more information.</p><note>Specify the schema, table, and column name using
+the same case as those names are stored in the system tables--that is, all
+upper case unless you used delimited identifiers when creating those database
+objects.</note><p><ph conref="refconrefs.dita#prod/productshortname"></ph> keeps
+track of the last increment value for a column in a cache. It also stores
+the value of what the next increment value will be for the column on disk
+in the <i>AUTOINCREMENTVALUE</i> column of the <i>SYS.SYSCOLUMNS</i> system
+table. Rolling back a transaction does not undo this value, and thus rolled-back
+transactions can leave "gaps" in the values automatically inserted into an
+identity column. <ph conref="refconrefs.dita#prod/productshortname"></ph> behaves
+this way to avoid locking a row in <i>SYS.SYSCOLUMNS</i> for the duration
+of a transaction and keeping concurrency high. </p><p><indexterm>Identity
+columns <indexterm>and triggers</indexterm></indexterm>When an insert happens
+within a triggered-SQL-statement, the value inserted by the triggered-SQL-statement
+into the identity column is available from <i>ConnectionInfo</i> only within
+the trigger code. The trigger code is also able to see the value inserted
+by the statement that caused the trigger to fire. However, the statement that
+caused the trigger to fire is not able to see the value inserted by the triggered-SQL-statement
+into the identity column. Likewise, triggers can be nested (or recursive).
+An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL
+statement that causes trigger T2 to fire. If both T1 and T2 insert rows into
+a table that cause <ph conref="refconrefs.dita#prod/productshortname"></ph> to
+insert into an identity column, trigger T1 cannot see the value caused by
+T2's insert, but T2 can see the value caused by T1's insert. Each nesting
+level can see increment values generated by itself and previous nesting levels,
+all the way to the top-level SQL statement that initiated the recursive triggers.
+You can only have 16 levels of trigger recursion.</p> </section>
</refbody>
</reference>
-