Helen Borrie
2016-04-26 22:38:36 UTC
Update of /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25
In directory sfp-cvs-1.v30.ch3.sourceforge.com:/tmp/cvs-serv24351
Modified Files:
fblangref25-commons.xml fblangref25-ddl.xml fblangref25.xml
Log Message:
Another batch of suggested edits from Aage Johansen
Index: fblangref25-commons.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25-commons.xml,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -d -r1.7 -r1.8
--- fblangref25-commons.xml 28 Mar 2016 19:03:03 -0000 1.7
+++ fblangref25-commons.xml 26 Apr 2016 22:38:33 -0000 1.8
@@ -928,6 +928,12 @@
</listitem>
</orderedlist>
</formalpara>
+
+ <note>
+ <title>Note about String Comparison</title>
+ <para>When <database>CHAR</database> and <database>VARCHAR</database> fields are compared for
+ equality, trailing spaces are ignored in all cases.</para>
+ </note>
<section id="fblangref25-commons-othercomppreds">
<title>Other Comparison Predicates</title>
Index: fblangref25-ddl.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25-ddl.xml,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -d -r1.10 -r1.11
--- fblangref25-ddl.xml 27 Mar 2016 21:20:47 -0000 1.10
+++ fblangref25-ddl.xml 26 Apr 2016 22:38:33 -0000 1.11
@@ -1792,13 +1792,13 @@
</section><!-- array column -->
<section id="fblangref25-ddl-tbl-constraints">
- <title>Constraints</title>
- <para>Four types of constraints can be specified. They are:
- <itemizedlist spacing="compact">
- <listitem>Primary key (<database>PRIMARY KEY</database>)</listitem>
- <listitem>Unique key (<database>UNIQUE</database>)</listitem>
- <listitem>Foreign key (<database>REFERENCES</database>)</listitem>
- <listitem><database>CHECK</database> constraint (<database>CHECK</database>)</listitem>
+ <title>Constraints</title>
+ <para>Four types of constraints can be specified. They are:
+ <itemizedlist spacing="compact">
+ <listitem>Primary key (<database>PRIMARY KEY</database>)</listitem>
+ <listitem>Unique key (<database>UNIQUE</database>)</listitem>
+ <listitem>Foreign key (<database>REFERENCES</database>)</listitem>
+ <listitem><database>CHECK</database> constraint (<database>CHECK</database>)</listitem>
</itemizedlist>
</para>
<para>Constraints can be specified at column level (<quote>column constraints</quote>)
@@ -1809,7 +1809,7 @@
whether the constraint is being defined at column or table level.
<itemizedlist>
<listitem>A column-level constraint is specified during a column definition, after
- all column attributes except <database>COLLATION</database> are specified, and can
+ all column attributes except <database>COLLATION</database> are specified, and can
involve only the column specified in that definition</listitem>
<listitem>Table-level constraints are specified after all of the column definitions.
They are a more flexible way to set constraints, since they can cater for constraints
@@ -1821,32 +1821,46 @@
<para>The system automatically creates the corresponding index for a primary key
(<database>PRIMARY KEY</database>), a unique key (<database>UNIQUE</database>) and a
foreign key (<database>REFERENCES</database> for a column-level constraint, <database>FOREIGN
- KEY REFERENCES</database> for one at the table level). If the name of the constraint is
- specified, the automatically created index will have this name (if there is no explicit
- <database>USING</database> clause).</para>
+ KEY REFERENCES</database> for one at the table level).</para>
- <section id="fblangref25-ddl-tbl-constraints-using">
- <title>The <database>USING</database> Clause</title>
- <para>The <database>USING</database> clause allows you to specify a user-defined name for the index that is
- created automatically and, optionally, to define the direction of the index—either
- ascending (the default) or descending.</para>
- </section>
+ <section id="fblangref25-ddl-tbl-constraints-names">
+ <title>Names for Constraints and Their Indexes</title>
+ <para>Column-level constraints and their indexes are named automatically:
+ <itemizedlist>
+ <listitem>The constraint name has the form <database>INTEG_n</database>, where
+ <database>n</database>represents one or more numerals</listitem>
+ <listitem>The index name has the form <database>RDB$PRIMARYn</database> (for a
+ primary key index), <database>RDB$FOREIGNn</database> (for a foreign key index)
+ or <database>RDB$n</database> (for a unique key index). Again, <database>n</database>
+ represents one or more numerals</listitem>
+ </itemizedlist>
+ </para>
+ <para>Automatic naming of table-level constraints and their indexes follows the same
+ pattern, unless the names are supplied explicitly.</para>
- <para>If the constraint specification clause does not define a name for a constraint,
- the DBMS will generate one for it automatically. However, you can specify a name for
- any constraint.</para>
+ <section id="fblangref25-ddl-tbl-constraints-named">
+ <title>Named Constraints</title>
+ <para>A constraint can be named explicitly if the <database>CONSTRAINT</database> clause
+ is used for its definition. While the <database>CONSTRAINT</database> clause is optional
+ for defining column-level constraints, it is mandatory for table-level. By default, the
+ constraint index will have the same name as the constraint. If a different name is wanted
+ for the constraint index, a <database>USING</database> clause can be included.</para>
+ </section>
- <section id="fblangref25-ddl-tbl-constraints-named">
- <title>Named Constraints</title>
- <para>The optional <database>CONSTRAINT</database> clause defines the name of a
- constraint.</para>
- </section>
+ <section id="fblangref25-ddl-tbl-constraints-using">
+ <title>The <database>USING</database> Clause</title>
+ <para>The <database>USING</database> clause allows you to specify a user-defined name for
+ the index that is created automatically and, optionally, to define the direction of the
+ index—either ascending (the default) or descending.</para>
+ </section>
+ </section><!-- names for constraints and their indexes -->
<section id="fblangref25-ddl-tbl-constraints-pk">
<title><database>PRIMARY KEY</database></title>
- <para>The <database>PRIMARY KEY</database> constraint is built on a field with the
- <database>NOT NULL</database> constraint specified for it and requires the column values
- to be unique. A table can have only one primary key.
+ <para>The <database>PRIMARY KEY</database> constraint is built on one or more
+ <firstterm>key columns</firstterm>, each column having the <database>NOT NULL</database>
+ constraint specified for it. The values across the key columns in any row must be unique.
+ A table can have only one primary key.
<itemizedlist spacing="compact">
<listitem>A single-column Primary Key can be defined as a column level or a
table-level constraint</listitem>
@@ -1941,8 +1955,8 @@
<section id="fblangref25-ddl-tbl-constraints-fkactions">
<title>Foreign Key Actions</title>
<para>With the sub-clauses <database>ON UPDATE</database> and <database>ON
- DELETE</database> it is possible to specify an action to be taken on
- the affected foreign key column(s) when referenced values in the master table
+ DELETE</database> it is possible to specify an action to be taken on
+ the affected foreign key column(s) when referenced values in the master table
are changed:
<itemizedlist>
<listitem><database>NO ACTION</database> (the default) - Nothing is
@@ -1959,8 +1973,8 @@
</itemizedlist>
The specified action, or the default <database>NO ACTION</database>, could cause
a Foreign Key column to become invalid. For example, it could get a value that is
- not present in the master table, or it could become <database>NULL</database> while
- the column has a <database>NOT NULL</database> constraint. Such conditions will
+ not present in the master table, or it could become <database>NULL</database> while
+ the column has a <database>NOT NULL</database> constraint. Such conditions will
cause the operation on the master table to fail with an error message.
</para>
<formalpara>
@@ -1973,7 +1987,7 @@
</programlisting></blockquote>
</formalpara>
</section> <!-- FK actions -->
- </section><!-- references -->
+ </section><!-- foreign key -->
<section id="fblangref25-ddl-tbl-constraints-check">
<title><database>CHECK</database> Constraint</title>
@@ -1987,7 +2001,7 @@
these actions may take place (UPDATE OR INSERT, MERGE).</para>
<important>
<para>A <database>CHECK</database> constraint on a domain-based column does not replace an
- existing <database>CHECK</database> condition on the domain, but becomes an addition to
+ existing <database>CHECK</database> condition on the domain, but becomes an addition to
it. The Firebird engine has no way, during definition, to verify that the extra
<database>CHECK</database> does not conflict with the existing one.</para>
</important>
@@ -2114,7 +2128,7 @@
mapping will not work. Paths enclosed in single or double quotes will not work, either.</listitem>
</itemizedlist>
</listitem>
- <listitem>If this parameter is set to <function>Full</function>, external files may be accessed
+ <listitem>If this parameter is set to <function>Full</function>, external files may be accessed
anywhere on the host file system. It creates a security vulnerability and is not recommended.</listitem>
</itemizedlist>
</para>
@@ -2134,7 +2148,7 @@
types are easily cast to and from strings whereas, unless the files are to be read by another Firebird
database, the native data types will appear to external applications as unparseable <quote>alphabetti</quote>.</para>
- <para>Of course, there are ways to manipulate typed data so as to generate output files from Firebird
+ <para>Of course, there are ways to manipulate typed data so as to generate output files from Firebird
that can be read directly as input files to other applications, using stored procedures, with or without
employing external tables. Such techniques are beyond the scope of a language reference. Here, we
provide some guidelines and tips for producing and working with simple text files, since the external
@@ -2143,19 +2157,19 @@
<section id="fblangref25-ddl-tbl-ext-format-delimiter">
<title>Row Delimiters</title>
- <para>Generally, external files are more useful if rows are separated by a delimiter, in the form
- of a <quote>newline</quote> sequence that is recognised by reader applications on the intended
- platform. For most contexts on Windows, it is the two-byte 'CRLF' sequence, carriage return (ASCII
- code decimal 13) and line feed (ASCII code decimal 10). On POSIX, LF on its own is usual; for some
- MacOSX applications, it may be LFCR. There are various ways to populate this delimiter column.
- In our example below, it is done by using a Before Insert trigger and the internal function
+ <para>Generally, external files are more useful if rows are separated by a delimiter, in the form
+ of a <quote>newline</quote> sequence that is recognised by reader applications on the intended
+ platform. For most contexts on Windows, it is the two-byte 'CRLF' sequence, carriage return (ASCII
+ code decimal 13) and line feed (ASCII code decimal 10). On POSIX, LF on its own is usual; for some
+ MacOSX applications, it may be LFCR. There are various ways to populate this delimiter column.
+ In our example below, it is done by using a Before Insert trigger and the internal function
<database>ASCII_CHAR</database>.</para>
</section> <!-- row delimiters -->
<bridgehead renderas="sect4">External Table Example</bridgehead>
<para>For our example, we will define an external log table that might be used by an exception handler
in a stored procedure or trigger. The external table is chosen because the messages from any
- handled exceptions will be retained in the log, even if the transaction that launched the process is
+ handled exceptions will be retained in the log, even if the transaction that launched the process is
eventually rolled back because of another, unhandled exception. For demonstration purposes, it has
just two data columns, a time stamp and a message. The third column stores the row delimiter:
<blockquote><programlisting>
@@ -2282,7 +2296,7 @@
</listitem>
<listitem>Creating a transaction-scoped global temporary table that uses a
foreign key to reference a connection-scoped global temporary table. The ON
- COMMIT sub-clause is optional because it is the default.
+ COMMIT sub-clause is optional because DELETE ROWS is the default.
<programlisting>
CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
ID INTEGER NOT NULL PRIMARY KEY,
@@ -2483,7 +2497,7 @@
<entry align="left">The condition of a CHECK constraint that will be satisfied if it
evaluates to TRUE or UNKNOWN/NULL</entry>
</row>
- <row valign="middle">
+ <row valign="bottom">
<entry align="center">collation</entry>
<entry align="left">Name of a collation sequence that is valid for
<replaceable>charset_name</replaceable>, if it is supplied with
@@ -2562,7 +2576,7 @@
</table>
<para>The <database>ALTER TABLE</database> statement changes the structure of an existing table.
With one <database>ALTER TABLE</database> statement it is possible to perform multiple operations,
- inserting/dropping columns and constraints and also altering column specifications.</para>
+ adding/dropping columns and constraints and also altering column specifications.</para>
<para>Multiple operations in an <database>ALTER TABLE</database> statement are separated with
commas.</para>
@@ -2974,11 +2988,16 @@
<section id="fblangref25-ddl-idx-drctn">
<title>Index Direction</title>
- <para>An index may be constructed from the lowest value to the highest (ascending order) or from the
- highest value to the lowest (descending order). The keywords <database>ASC[ENDING]</database> and
- <database>DESC[ENDING]</database> are used to specify the direction of the index. The default index order is
- <database>ASC[ENDING]</database>.</para>
- </section>
+ <para>All indexes in Firebird are uni-directional. An index may be constructed from the lowest value
+ to the highest (ascending order) or from the highest value to the lowest (descending order).
+ The keywords <database>ASC[ENDING]</database> and <database>DESC[ENDING]</database> are used to
+ specify the direction of the index. The default index order is <database>ASC[ENDING]</database>.
+ It is quite valid to define both an ascending and a descending index on the same column or key set.</para>
+ <tip>
+ <para>A descending index can be useful on a column that will be subjected to searches on the
+ high values (<quote>newest</quote>, maximum, etc.)</para>
+ </tip>
+ </section>
<section id="fblangref25-ddl-idx-exprssn">
<title>Computed (Expression) Indexes</title>
@@ -3194,12 +3213,23 @@
</table>
<para>The <database>ALTER INDEX</database> statement activates or deactivates an index.
- There is no facility om this statement for altering any attributes of the index.
+ There is no facility on this statement for altering any attributes of the index.
<itemizedlist>
- <listitem>With the <database>INACTIVE</database> option, the index is switched from the
- active to inactive state. The effect is similar to the <database>DROP INDEX</database> statement
- except that the index definition remains in the database. Altering a constraint index to
- inactive is not permitted.
+ <listitem>
+ <para>With the <database>INACTIVE</database> option, the index is switched from the
+ active to inactive state. The effect is similar to the <database>DROP INDEX</database> statement
+ except that the index definition remains in the database. Altering a constraint index to the
+ inactive state is not permitted.</para>
+ <para>An active index can be deactivated if there are no queries using that index; otherwise,
+ an <quote>object in use</quote> error is returned.</para>
+ <para>Activating an inactive index is also safe. However, if there are active transactions
+ modifying the table, the transaction containing the <database>ALTER INDEX</database>
+ statement will fail if it has the <database>NOWAIT</database> attribute. If the transaction is
+ in <database>WAIT</database> mode, it will wait for completion of concurrent transactions.</para>
+ <para>On the other side of the coin, if our <database>ALTER INDEX</database> succeeds and starts
+ to rebuild the index at <database>COMMIT</database>, other transactions modifying that table
+ will fail or wait, according to their <database>WAIT/NO WAIT</database> attributes. The situation
+ is exactly the same for <database>CREATE INDEX</database>.</para>
<note>
<title>How is it Useful?</title>
<para>It might be useful to switch an index to the inactive state whilst inserting,
@@ -3213,7 +3243,8 @@
<title>How is it Useful?</title>
<para>Even if the index is <emphasis>active</emphasis> when <database>ALTER INDEX ... ACTIVE</database>
is executed, the index will be rebuilt. Rebuilding indexes can be a useful piece of houskeeping to
- do, occasionally, on the indexes of a large table in a database that is infrequently restored.</para>
+ do, occasionally, on the indexes of a large table in a database that has frequent inserts, updates or
+ deletes but is infrequently restored.</para>
</note>
</listitem>
</itemizedlist>
@@ -3227,7 +3258,6 @@
indexes as it does with others, as an index rebuilding tool.</para>
</section>
-
<para>Only the table owner and <link linkend="fblangref25-security-administrators">administrators</link>
have the authority to use <database>ALTER INDEX</database>.</para>
@@ -3358,11 +3388,9 @@
because it is impossible to select more than one row for each value of an index key if
it is used. Keeping the selectivity of an index up to date is important for the optimizer's
choices in seeking the most optimal query plan.</para>
- <para>Index statistics in Firebird are not automatically recalculated, either after a lot
- of data has been modified or under any other conditions.</para>
- <para>It may be necessary to recalculate the selectivity of an index after inserting,
- updating or deleting a large number of records in the table, because the selectivity tends
- to become outdated.
+ <para>Index statistics in Firebird are not automatically recalculated in response to large
+ batches of inserts, updates or deletions. It may be beneficial to recalculate the selectivity
+ of an index after such operations because the selectivity tends to become outdated.
<note>
<para>The statements <database>CREATE INDEX</database> and <database>ALTER INDEX
ACTIVE</database> both store index statistics that completely correspond to the
@@ -3370,8 +3398,12 @@
</note>
</para>
</section>
+
<para>The selectivity of an index can be recalculated by the owner of the table
- or an <link linkend="fblangref25-security-administrators">administrator</link>.</para>
+ or an <link linkend="fblangref25-security-administrators">administrator</link>. It can be
+ performed under concurrent load without risk of corruption. However, be aware that,
+ under concurrent load, the newly calculated statistics could become outdated as soon as
+ <database>SET STATISTICS</database> finishes.</para>
<formalpara>
<title>Example Using SET STATISTICS</title>
@@ -3379,8 +3411,8 @@
<blockquote><programlisting>
SET STATISTICS INDEX IDX_UPDATER;
</programlisting></blockquote>
- </para>
- </formalpara>
+ </para>
+ </formalpara>
<formalpara><title>See also</title>
<para> <link linkend="fblangref25-ddl-idx-create"><database>CREATE INDEX</database></link>,
<link linkend="fblangref25-ddl-idx-altridx"><database>ALTER INDEX</database></link>
@@ -3502,7 +3534,7 @@
aggregate functions, such as <database>MIN</database>, <database>MAX</database>, <database>AVG</database>,
<database>SUM</database>, <database>COUNT</database>, <database>LIST</database></listitem>
<listitem>the <database>SELECT</database> statement contains no <database>ORDER BY</database> or
- <database>GROUP BY</database> or GROUP BY clause</listitem>
+ <database>GROUP BY</database> clause</listitem>
<listitem>the <database>SELECT</database> statement does not include the keyword <database>DISTINCT</database>
or row-restrictive keywords such as <database>ROWS</database>, <database>FIRST</database>,
<database>SKIP</database></listitem>
Index: fblangref25.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25.xml,v
retrieving revision 1.20
retrieving revision 1.21
diff -u -d -r1.20 -r1.21
--- fblangref25.xml 28 Mar 2016 19:03:03 -0000 1.20
+++ fblangref25.xml 26 Apr 2016 22:38:33 -0000 1.21
@@ -5,7 +5,7 @@
<bookinfo>
<title>Firebird 2.5 Language Reference</title>
<subtitle>Beta Release 1</subtitle>
- <edition>29 March 2016, document version 0.903</edition>
+ <edition>26 April 2016, document version 0.904</edition>
<authorgroup>
<author>
<firstname>Dmitry</firstname>
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
Firebird-checkins mailing list
Firebird-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-checkins
In directory sfp-cvs-1.v30.ch3.sourceforge.com:/tmp/cvs-serv24351
Modified Files:
fblangref25-commons.xml fblangref25-ddl.xml fblangref25.xml
Log Message:
Another batch of suggested edits from Aage Johansen
Index: fblangref25-commons.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25-commons.xml,v
retrieving revision 1.7
retrieving revision 1.8
diff -u -d -r1.7 -r1.8
--- fblangref25-commons.xml 28 Mar 2016 19:03:03 -0000 1.7
+++ fblangref25-commons.xml 26 Apr 2016 22:38:33 -0000 1.8
@@ -928,6 +928,12 @@
</listitem>
</orderedlist>
</formalpara>
+
+ <note>
+ <title>Note about String Comparison</title>
+ <para>When <database>CHAR</database> and <database>VARCHAR</database> fields are compared for
+ equality, trailing spaces are ignored in all cases.</para>
+ </note>
<section id="fblangref25-commons-othercomppreds">
<title>Other Comparison Predicates</title>
Index: fblangref25-ddl.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25-ddl.xml,v
retrieving revision 1.10
retrieving revision 1.11
diff -u -d -r1.10 -r1.11
--- fblangref25-ddl.xml 27 Mar 2016 21:20:47 -0000 1.10
+++ fblangref25-ddl.xml 26 Apr 2016 22:38:33 -0000 1.11
@@ -1792,13 +1792,13 @@
</section><!-- array column -->
<section id="fblangref25-ddl-tbl-constraints">
- <title>Constraints</title>
- <para>Four types of constraints can be specified. They are:
- <itemizedlist spacing="compact">
- <listitem>Primary key (<database>PRIMARY KEY</database>)</listitem>
- <listitem>Unique key (<database>UNIQUE</database>)</listitem>
- <listitem>Foreign key (<database>REFERENCES</database>)</listitem>
- <listitem><database>CHECK</database> constraint (<database>CHECK</database>)</listitem>
+ <title>Constraints</title>
+ <para>Four types of constraints can be specified. They are:
+ <itemizedlist spacing="compact">
+ <listitem>Primary key (<database>PRIMARY KEY</database>)</listitem>
+ <listitem>Unique key (<database>UNIQUE</database>)</listitem>
+ <listitem>Foreign key (<database>REFERENCES</database>)</listitem>
+ <listitem><database>CHECK</database> constraint (<database>CHECK</database>)</listitem>
</itemizedlist>
</para>
<para>Constraints can be specified at column level (<quote>column constraints</quote>)
@@ -1809,7 +1809,7 @@
whether the constraint is being defined at column or table level.
<itemizedlist>
<listitem>A column-level constraint is specified during a column definition, after
- all column attributes except <database>COLLATION</database> are specified, and can
+ all column attributes except <database>COLLATION</database> are specified, and can
involve only the column specified in that definition</listitem>
<listitem>Table-level constraints are specified after all of the column definitions.
They are a more flexible way to set constraints, since they can cater for constraints
@@ -1821,32 +1821,46 @@
<para>The system automatically creates the corresponding index for a primary key
(<database>PRIMARY KEY</database>), a unique key (<database>UNIQUE</database>) and a
foreign key (<database>REFERENCES</database> for a column-level constraint, <database>FOREIGN
- KEY REFERENCES</database> for one at the table level). If the name of the constraint is
- specified, the automatically created index will have this name (if there is no explicit
- <database>USING</database> clause).</para>
+ KEY REFERENCES</database> for one at the table level).</para>
- <section id="fblangref25-ddl-tbl-constraints-using">
- <title>The <database>USING</database> Clause</title>
- <para>The <database>USING</database> clause allows you to specify a user-defined name for the index that is
- created automatically and, optionally, to define the direction of the index—either
- ascending (the default) or descending.</para>
- </section>
+ <section id="fblangref25-ddl-tbl-constraints-names">
+ <title>Names for Constraints and Their Indexes</title>
+ <para>Column-level constraints and their indexes are named automatically:
+ <itemizedlist>
+ <listitem>The constraint name has the form <database>INTEG_n</database>, where
+ <database>n</database>represents one or more numerals</listitem>
+ <listitem>The index name has the form <database>RDB$PRIMARYn</database> (for a
+ primary key index), <database>RDB$FOREIGNn</database> (for a foreign key index)
+ or <database>RDB$n</database> (for a unique key index). Again, <database>n</database>
+ represents one or more numerals</listitem>
+ </itemizedlist>
+ </para>
+ <para>Automatic naming of table-level constraints and their indexes follows the same
+ pattern, unless the names are supplied explicitly.</para>
- <para>If the constraint specification clause does not define a name for a constraint,
- the DBMS will generate one for it automatically. However, you can specify a name for
- any constraint.</para>
+ <section id="fblangref25-ddl-tbl-constraints-named">
+ <title>Named Constraints</title>
+ <para>A constraint can be named explicitly if the <database>CONSTRAINT</database> clause
+ is used for its definition. While the <database>CONSTRAINT</database> clause is optional
+ for defining column-level constraints, it is mandatory for table-level. By default, the
+ constraint index will have the same name as the constraint. If a different name is wanted
+ for the constraint index, a <database>USING</database> clause can be included.</para>
+ </section>
- <section id="fblangref25-ddl-tbl-constraints-named">
- <title>Named Constraints</title>
- <para>The optional <database>CONSTRAINT</database> clause defines the name of a
- constraint.</para>
- </section>
+ <section id="fblangref25-ddl-tbl-constraints-using">
+ <title>The <database>USING</database> Clause</title>
+ <para>The <database>USING</database> clause allows you to specify a user-defined name for
+ the index that is created automatically and, optionally, to define the direction of the
+ index—either ascending (the default) or descending.</para>
+ </section>
+ </section><!-- names for constraints and their indexes -->
<section id="fblangref25-ddl-tbl-constraints-pk">
<title><database>PRIMARY KEY</database></title>
- <para>The <database>PRIMARY KEY</database> constraint is built on a field with the
- <database>NOT NULL</database> constraint specified for it and requires the column values
- to be unique. A table can have only one primary key.
+ <para>The <database>PRIMARY KEY</database> constraint is built on one or more
+ <firstterm>key columns</firstterm>, each column having the <database>NOT NULL</database>
+ constraint specified for it. The values across the key columns in any row must be unique.
+ A table can have only one primary key.
<itemizedlist spacing="compact">
<listitem>A single-column Primary Key can be defined as a column level or a
table-level constraint</listitem>
@@ -1941,8 +1955,8 @@
<section id="fblangref25-ddl-tbl-constraints-fkactions">
<title>Foreign Key Actions</title>
<para>With the sub-clauses <database>ON UPDATE</database> and <database>ON
- DELETE</database> it is possible to specify an action to be taken on
- the affected foreign key column(s) when referenced values in the master table
+ DELETE</database> it is possible to specify an action to be taken on
+ the affected foreign key column(s) when referenced values in the master table
are changed:
<itemizedlist>
<listitem><database>NO ACTION</database> (the default) - Nothing is
@@ -1959,8 +1973,8 @@
</itemizedlist>
The specified action, or the default <database>NO ACTION</database>, could cause
a Foreign Key column to become invalid. For example, it could get a value that is
- not present in the master table, or it could become <database>NULL</database> while
- the column has a <database>NOT NULL</database> constraint. Such conditions will
+ not present in the master table, or it could become <database>NULL</database> while
+ the column has a <database>NOT NULL</database> constraint. Such conditions will
cause the operation on the master table to fail with an error message.
</para>
<formalpara>
@@ -1973,7 +1987,7 @@
</programlisting></blockquote>
</formalpara>
</section> <!-- FK actions -->
- </section><!-- references -->
+ </section><!-- foreign key -->
<section id="fblangref25-ddl-tbl-constraints-check">
<title><database>CHECK</database> Constraint</title>
@@ -1987,7 +2001,7 @@
these actions may take place (UPDATE OR INSERT, MERGE).</para>
<important>
<para>A <database>CHECK</database> constraint on a domain-based column does not replace an
- existing <database>CHECK</database> condition on the domain, but becomes an addition to
+ existing <database>CHECK</database> condition on the domain, but becomes an addition to
it. The Firebird engine has no way, during definition, to verify that the extra
<database>CHECK</database> does not conflict with the existing one.</para>
</important>
@@ -2114,7 +2128,7 @@
mapping will not work. Paths enclosed in single or double quotes will not work, either.</listitem>
</itemizedlist>
</listitem>
- <listitem>If this parameter is set to <function>Full</function>, external files may be accessed
+ <listitem>If this parameter is set to <function>Full</function>, external files may be accessed
anywhere on the host file system. It creates a security vulnerability and is not recommended.</listitem>
</itemizedlist>
</para>
@@ -2134,7 +2148,7 @@
types are easily cast to and from strings whereas, unless the files are to be read by another Firebird
database, the native data types will appear to external applications as unparseable <quote>alphabetti</quote>.</para>
- <para>Of course, there are ways to manipulate typed data so as to generate output files from Firebird
+ <para>Of course, there are ways to manipulate typed data so as to generate output files from Firebird
that can be read directly as input files to other applications, using stored procedures, with or without
employing external tables. Such techniques are beyond the scope of a language reference. Here, we
provide some guidelines and tips for producing and working with simple text files, since the external
@@ -2143,19 +2157,19 @@
<section id="fblangref25-ddl-tbl-ext-format-delimiter">
<title>Row Delimiters</title>
- <para>Generally, external files are more useful if rows are separated by a delimiter, in the form
- of a <quote>newline</quote> sequence that is recognised by reader applications on the intended
- platform. For most contexts on Windows, it is the two-byte 'CRLF' sequence, carriage return (ASCII
- code decimal 13) and line feed (ASCII code decimal 10). On POSIX, LF on its own is usual; for some
- MacOSX applications, it may be LFCR. There are various ways to populate this delimiter column.
- In our example below, it is done by using a Before Insert trigger and the internal function
+ <para>Generally, external files are more useful if rows are separated by a delimiter, in the form
+ of a <quote>newline</quote> sequence that is recognised by reader applications on the intended
+ platform. For most contexts on Windows, it is the two-byte 'CRLF' sequence, carriage return (ASCII
+ code decimal 13) and line feed (ASCII code decimal 10). On POSIX, LF on its own is usual; for some
+ MacOSX applications, it may be LFCR. There are various ways to populate this delimiter column.
+ In our example below, it is done by using a Before Insert trigger and the internal function
<database>ASCII_CHAR</database>.</para>
</section> <!-- row delimiters -->
<bridgehead renderas="sect4">External Table Example</bridgehead>
<para>For our example, we will define an external log table that might be used by an exception handler
in a stored procedure or trigger. The external table is chosen because the messages from any
- handled exceptions will be retained in the log, even if the transaction that launched the process is
+ handled exceptions will be retained in the log, even if the transaction that launched the process is
eventually rolled back because of another, unhandled exception. For demonstration purposes, it has
just two data columns, a time stamp and a message. The third column stores the row delimiter:
<blockquote><programlisting>
@@ -2282,7 +2296,7 @@
</listitem>
<listitem>Creating a transaction-scoped global temporary table that uses a
foreign key to reference a connection-scoped global temporary table. The ON
- COMMIT sub-clause is optional because it is the default.
+ COMMIT sub-clause is optional because DELETE ROWS is the default.
<programlisting>
CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
ID INTEGER NOT NULL PRIMARY KEY,
@@ -2483,7 +2497,7 @@
<entry align="left">The condition of a CHECK constraint that will be satisfied if it
evaluates to TRUE or UNKNOWN/NULL</entry>
</row>
- <row valign="middle">
+ <row valign="bottom">
<entry align="center">collation</entry>
<entry align="left">Name of a collation sequence that is valid for
<replaceable>charset_name</replaceable>, if it is supplied with
@@ -2562,7 +2576,7 @@
</table>
<para>The <database>ALTER TABLE</database> statement changes the structure of an existing table.
With one <database>ALTER TABLE</database> statement it is possible to perform multiple operations,
- inserting/dropping columns and constraints and also altering column specifications.</para>
+ adding/dropping columns and constraints and also altering column specifications.</para>
<para>Multiple operations in an <database>ALTER TABLE</database> statement are separated with
commas.</para>
@@ -2974,11 +2988,16 @@
<section id="fblangref25-ddl-idx-drctn">
<title>Index Direction</title>
- <para>An index may be constructed from the lowest value to the highest (ascending order) or from the
- highest value to the lowest (descending order). The keywords <database>ASC[ENDING]</database> and
- <database>DESC[ENDING]</database> are used to specify the direction of the index. The default index order is
- <database>ASC[ENDING]</database>.</para>
- </section>
+ <para>All indexes in Firebird are uni-directional. An index may be constructed from the lowest value
+ to the highest (ascending order) or from the highest value to the lowest (descending order).
+ The keywords <database>ASC[ENDING]</database> and <database>DESC[ENDING]</database> are used to
+ specify the direction of the index. The default index order is <database>ASC[ENDING]</database>.
+ It is quite valid to define both an ascending and a descending index on the same column or key set.</para>
+ <tip>
+ <para>A descending index can be useful on a column that will be subjected to searches on the
+ high values (<quote>newest</quote>, maximum, etc.)</para>
+ </tip>
+ </section>
<section id="fblangref25-ddl-idx-exprssn">
<title>Computed (Expression) Indexes</title>
@@ -3194,12 +3213,23 @@
</table>
<para>The <database>ALTER INDEX</database> statement activates or deactivates an index.
- There is no facility om this statement for altering any attributes of the index.
+ There is no facility on this statement for altering any attributes of the index.
<itemizedlist>
- <listitem>With the <database>INACTIVE</database> option, the index is switched from the
- active to inactive state. The effect is similar to the <database>DROP INDEX</database> statement
- except that the index definition remains in the database. Altering a constraint index to
- inactive is not permitted.
+ <listitem>
+ <para>With the <database>INACTIVE</database> option, the index is switched from the
+ active to inactive state. The effect is similar to the <database>DROP INDEX</database> statement
+ except that the index definition remains in the database. Altering a constraint index to the
+ inactive state is not permitted.</para>
+ <para>An active index can be deactivated if there are no queries using that index; otherwise,
+ an <quote>object in use</quote> error is returned.</para>
+ <para>Activating an inactive index is also safe. However, if there are active transactions
+ modifying the table, the transaction containing the <database>ALTER INDEX</database>
+ statement will fail if it has the <database>NOWAIT</database> attribute. If the transaction is
+ in <database>WAIT</database> mode, it will wait for completion of concurrent transactions.</para>
+ <para>On the other side of the coin, if our <database>ALTER INDEX</database> succeeds and starts
+ to rebuild the index at <database>COMMIT</database>, other transactions modifying that table
+ will fail or wait, according to their <database>WAIT/NO WAIT</database> attributes. The situation
+ is exactly the same for <database>CREATE INDEX</database>.</para>
<note>
<title>How is it Useful?</title>
<para>It might be useful to switch an index to the inactive state whilst inserting,
@@ -3213,7 +3243,8 @@
<title>How is it Useful?</title>
<para>Even if the index is <emphasis>active</emphasis> when <database>ALTER INDEX ... ACTIVE</database>
is executed, the index will be rebuilt. Rebuilding indexes can be a useful piece of houskeeping to
- do, occasionally, on the indexes of a large table in a database that is infrequently restored.</para>
+ do, occasionally, on the indexes of a large table in a database that has frequent inserts, updates or
+ deletes but is infrequently restored.</para>
</note>
</listitem>
</itemizedlist>
@@ -3227,7 +3258,6 @@
indexes as it does with others, as an index rebuilding tool.</para>
</section>
-
<para>Only the table owner and <link linkend="fblangref25-security-administrators">administrators</link>
have the authority to use <database>ALTER INDEX</database>.</para>
@@ -3358,11 +3388,9 @@
because it is impossible to select more than one row for each value of an index key if
it is used. Keeping the selectivity of an index up to date is important for the optimizer's
choices in seeking the most optimal query plan.</para>
- <para>Index statistics in Firebird are not automatically recalculated, either after a lot
- of data has been modified or under any other conditions.</para>
- <para>It may be necessary to recalculate the selectivity of an index after inserting,
- updating or deleting a large number of records in the table, because the selectivity tends
- to become outdated.
+ <para>Index statistics in Firebird are not automatically recalculated in response to large
+ batches of inserts, updates or deletions. It may be beneficial to recalculate the selectivity
+ of an index after such operations because the selectivity tends to become outdated.
<note>
<para>The statements <database>CREATE INDEX</database> and <database>ALTER INDEX
ACTIVE</database> both store index statistics that completely correspond to the
@@ -3370,8 +3398,12 @@
</note>
</para>
</section>
+
<para>The selectivity of an index can be recalculated by the owner of the table
- or an <link linkend="fblangref25-security-administrators">administrator</link>.</para>
+ or an <link linkend="fblangref25-security-administrators">administrator</link>. It can be
+ performed under concurrent load without risk of corruption. However, be aware that,
+ under concurrent load, the newly calculated statistics could become outdated as soon as
+ <database>SET STATISTICS</database> finishes.</para>
<formalpara>
<title>Example Using SET STATISTICS</title>
@@ -3379,8 +3411,8 @@
<blockquote><programlisting>
SET STATISTICS INDEX IDX_UPDATER;
</programlisting></blockquote>
- </para>
- </formalpara>
+ </para>
+ </formalpara>
<formalpara><title>See also</title>
<para> <link linkend="fblangref25-ddl-idx-create"><database>CREATE INDEX</database></link>,
<link linkend="fblangref25-ddl-idx-altridx"><database>ALTER INDEX</database></link>
@@ -3502,7 +3534,7 @@
aggregate functions, such as <database>MIN</database>, <database>MAX</database>, <database>AVG</database>,
<database>SUM</database>, <database>COUNT</database>, <database>LIST</database></listitem>
<listitem>the <database>SELECT</database> statement contains no <database>ORDER BY</database> or
- <database>GROUP BY</database> or GROUP BY clause</listitem>
+ <database>GROUP BY</database> clause</listitem>
<listitem>the <database>SELECT</database> statement does not include the keyword <database>DISTINCT</database>
or row-restrictive keywords such as <database>ROWS</database>, <database>FIRST</database>,
<database>SKIP</database></listitem>
Index: fblangref25.xml
===================================================================
RCS file: /cvsroot/firebird/manual/src/docs/refdocs/langref/fblangref25/fblangref25.xml,v
retrieving revision 1.20
retrieving revision 1.21
diff -u -d -r1.20 -r1.21
--- fblangref25.xml 28 Mar 2016 19:03:03 -0000 1.20
+++ fblangref25.xml 26 Apr 2016 22:38:33 -0000 1.21
@@ -5,7 +5,7 @@
<bookinfo>
<title>Firebird 2.5 Language Reference</title>
<subtitle>Beta Release 1</subtitle>
- <edition>29 March 2016, document version 0.903</edition>
+ <edition>26 April 2016, document version 0.904</edition>
<authorgroup>
<author>
<firstname>Dmitry</firstname>
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
Firebird-checkins mailing list
Firebird-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-checkins