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 ch...@apache.org on 2014/05/29 21:04:12 UTC
svn commit: r1598372 - in /db/derby/docs/trunk/src: devguide/ ref/
Author: chaase3
Date: Thu May 29 19:04:11 2014
New Revision: 1598372
URL: http://svn.apache.org/r1598372
Log:
DERBY-6571 Document deferrable constraints
Modified one Developer's Guide topic; in the Reference Manual, modified 7 topics and the map file, and added 3 new topics.
Patches: DERBY-6571-5.diff
Added:
db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita (with props)
db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita (with props)
db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita (with props)
Modified:
db/derby/docs/trunk/src/devguide/cdevconcepts838850.dita
db/derby/docs/trunk/src/ref/crefsqlj35312.dita
db/derby/docs/trunk/src/ref/refderby.ditamap
db/derby/docs/trunk/src/ref/rrefcreatefunctionstatement.dita
db/derby/docs/trunk/src/ref/rrefcreateprocedurestatement.dita
db/derby/docs/trunk/src/ref/rrefsistabs23241.dita
db/derby/docs/trunk/src/ref/rrefsqlj13590.dita
db/derby/docs/trunk/src/ref/rrefsqlj16095.dita
db/derby/docs/trunk/src/ref/rrefsqlj42154.dita
Modified: db/derby/docs/trunk/src/devguide/cdevconcepts838850.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/cdevconcepts838850.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/devguide/cdevconcepts838850.dita (original)
+++ db/derby/docs/trunk/src/devguide/cdevconcepts838850.dita Thu May 29 19:04:11 2014
@@ -42,5 +42,10 @@ is <codeph>true</codeph> and <codeph><i>
with them need to be closed explicitly. A commit will not close such <codeph><i>ResultSet</i></codeph> objects.
It also releases any database locks currently held by the <codeph><i>Connection</i></codeph>,
whether or not these objects were created in different threads.</p>
+<p>Any outstanding violations of deferred constraints will be checked at commit
+time, so the call to <codeph><i>Connection.commit</i></codeph> may throw an
+exception. See "CONSTRAINT clause" in the
+<ph conref="../conrefs.dita#pub/citref"></ph> for information about deferrable
+constraints.</p>
</conbody>
</concept>
Modified: db/derby/docs/trunk/src/ref/crefsqlj35312.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/crefsqlj35312.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/crefsqlj35312.dita (original)
+++ db/derby/docs/trunk/src/ref/crefsqlj35312.dita Thu May 29 19:04:11 2014
@@ -21,7 +21,8 @@ limitations under the License.
<concept id="crefsqlj35312" xml:lang="en-us">
<title>SET statements</title>
<conbody>
-<p>Use the SET statements to set the current role, schema, or isolation level.</p>
+<p>Use the SET statements to set the current deferrability for constraints or to
+set the current role, schema, or isolation level.</p>
</conbody>
</concept>
Modified: db/derby/docs/trunk/src/ref/refderby.ditamap
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/refderby.ditamap?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/refderby.ditamap (original)
+++ db/derby/docs/trunk/src/ref/refderby.ditamap Thu May 29 19:04:11 2014
@@ -681,6 +681,7 @@ limitations under the License.
<topicref href="rrefsqljrevoke.dita" navtitle="REVOKE statement"></topicref>
<topicref href="rrefsqlj41360.dita" navtitle="SELECT statement"></topicref>
<topicref collection-type="family" href="crefsqlj35312.dita" navtitle="SET statements">
+<topicref href="rrefsqljsetconstr.dita" navtitle="SET CONSTRAINTS statement"/>
<topicref href="rrefsqlj41180.dita" navtitle="SET CURRENT ISOLATION statement">
</topicref>
<topicref href="rrefsetrole.dita" navtitle="SET ROLE statement"></topicref>
@@ -694,6 +695,7 @@ limitations under the License.
<topicref href="rrefsqlj16095.dita" navtitle="columnLevelConstraint"></topicref>
<topicref href="rrefsqlj42154.dita" navtitle="tableLevelConstraint"></topicref>
<topicref href="rrefsqlj16357.dita" navtitle="REFERENCES clause"></topicref>
+<topicref href="rrefsqljconstrchar.dita" navtitle="constraintCharacteristics"/>
</topicref>
<topicref href="rrefsqljexternalname.dita" navtitle="EXTERNAL NAME clause"></topicref>
<topicref href="rrefsqlj31783.dita" navtitle="FOR UPDATE clause"></topicref>
@@ -1322,6 +1324,7 @@ URL syntax"></topicref>
</topicref>
<topicref href="rrefjta1003463.dita" navtitle="XAConnections, user names and passwords">
</topicref>
+<topicref href="rrefjtadefconstr.dita" navtitle="XA transactions and deferred constraints"/>
</topicref>
</topicref>
<topicref href="rrefjta18596.dita" navtitle="javax.sql: JDBC Interfaces">
Modified: db/derby/docs/trunk/src/ref/rrefcreatefunctionstatement.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefcreatefunctionstatement.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefcreatefunctionstatement.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefcreatefunctionstatement.dita Thu May 29 19:04:11 2014
@@ -36,7 +36,9 @@ other users. The EXECUTE privileges cann
database owners.</p>
<p>For details on how <ph conref="../conrefs.dita#prod/productshortname"></ph>
matches procedures to Java methods, see
-<xref href="rrefsqljargmatching.dita#rrefsqljargmatching"></xref>.</p>
+<xref href="rrefsqljargmatching.dita#rrefsqljargmatching"></xref>. For
+information on how functions interact with deferrable constraints, see
+<xref href="rrefsqlj13590.dita#rrefsqlj13590/deferrableconstraints"></xref>.</p>
</section>
<refsyn><title>Syntax</title> <codeblock><b>CREATE FUNCTION <i><xref
href="rreffunctionname.dita#rreffunctionname">functionName</xref></i> ( [ <i><xref
Modified: db/derby/docs/trunk/src/ref/rrefcreateprocedurestatement.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefcreateprocedurestatement.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefcreateprocedurestatement.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefcreateprocedurestatement.dita Thu May 29 19:04:11 2014
@@ -35,7 +35,9 @@ on the procedure, and are able to grant
EXECUTE privileges cannot be revoked from the procedure and database owners.</p>
<p>For details on how <ph conref="../conrefs.dita#prod/productshortname"></ph>
matches procedures to Java methods, see
-<xref href="rrefsqljargmatching.dita#rrefsqljargmatching"></xref>.</p>
+<xref href="rrefsqljargmatching.dita#rrefsqljargmatching"></xref>. For
+information on how stored procedures interact with deferrable constraints, see
+<xref href="rrefsqlj13590.dita#rrefsqlj13590/deferrableconstraints"></xref>.</p>
</section>
<refsyn><title>Syntax</title>
<codeblock><b>CREATE PROCEDURE <i><xref
Added: db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita?rev=1598372&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita Thu May 29 19:04:11 2014
@@ -0,0 +1,33 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rrefjtadefconstr" xml:lang="en-us">
+<title>XA transactions and deferred constraints</title>
+<refbody>
+<section>
+<p>If an application calls <i>XAResource.prepare(Xid)</i>, any constraints with
+a constraint mode of DEFERRED are checked. If there is a violation,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> throws
+<i>XAException.XA_RBINTEGRITY</i>, and the XA transaction is rolled back. See
+<xref href="rrefsqlj13590.dita"/> and <xref href="rrefsqljsetconstr.dita"/> for
+more information about deferrable constraints.</p>
+</section>
+</refbody>
+</reference>
Propchange: db/derby/docs/trunk/src/ref/rrefjtadefconstr.dita
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/docs/trunk/src/ref/rrefsistabs23241.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsistabs23241.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsistabs23241.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsistabs23241.dita Thu May 29 19:04:11 2014
@@ -94,7 +94,9 @@ with SYSSCHEMAS.SCHEMAID)</entry>
<entry colname="2">CHAR</entry>
<entry colname="3">1</entry>
<entry colname="4">false</entry>
-<entry colname="5"><i>'E'</i> for enabled, <i>'D'</i> for disabled</entry>
+<entry colname="5"><i>'E'</i> (not deferrable initially immediate), <i>'i'</i>
+(deferrable initially immediate), or <i>'e'</i> (deferrable initially
+deferred)</entry>
</row>
<row>
<entry colname="1">REFERENCECOUNT</entry>
Modified: db/derby/docs/trunk/src/ref/rrefsqlj13590.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj13590.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj13590.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj13590.dita Thu May 29 19:04:11 2014
@@ -83,7 +83,35 @@ See "Using SQL standard authorization" a
constraints" in the <ph conref="../conrefs.dita#pub/citdevelop"></ph> for
details.</p>
</section>
-<refsyn><title>Syntax</title> </refsyn>
+<section id="deferrableconstraints"><title>Deferrable constraints</title>
+<p>Constraints can be <i>deferred</i>, meaning that
+<ph conref="../conrefs.dita#prod/productshortname"></ph> does not check
+constraints immediately. By default, a constraint is checked as soon as a
+statement completes. Deferrable constraints allow temporary breaches of
+constraints for more flexible insert and update operations.</p>
+<p>With deferrable constraints, the checking of one or more constraints does not
+take place until it is explicitly or implicitly requested using one of the
+following mechanisms:</p>
+<ul>
+<li>The transaction ends (a commit operation takes place)</li>
+<li>A <xref href="rrefsqljsetconstr.dita">SET CONSTRAINTS statement</xref> which
+sets the constraint mode to IMMEDIATE is executed</li>
+<li>A return from a stored procedure or function reverts the constraint mode to
+IMMEDIATE</li>
+</ul>
+<p>The point at which a deferrable constraint is checked is referred to as the
+<i>deferred checking time</i>.</p>
+<p>If the constraint mode of a constraint is IMMEDIATE before a call to a stored
+procedure or function, and the stored procedure or function sets the constraint
+mode of that constraint to DEFERRED, the constraint mode is implicitly reset to
+IMMEDIATE on return from the stored procedure. This happens because the
+constraint mode is pushed on a stack when we enter the stored procedure or
+function (as are other session state variables, like the current role). If a
+constraint violation happens as a result, the transaction is rolled back and an
+exception is thrown.</p>
+<p>See <xref href="rrefsqlj13590.dita#rrefsqlj13590/referactions"></xref> for
+information about the behavior of deferrable foreign keys.</p>
+</section>
<section><title>Primary key constraints</title> <p><indexterm>Primary
key constraints</indexterm>A primary
key defines the set of columns that uniquely identifies rows in a table.</p> <p>When
@@ -181,8 +209,10 @@ any of the following: <ul>
<li>Subqueries</li>
<li>User Functions (such as USER, SESSION_USER, CURRENT_USER)</li>
</ul></p> </section>
-<section><title>Referential actions</title> <p><indexterm>Referential actions</indexterm><indexterm>Constraint
-clause<indexterm>referential actions in</indexterm></indexterm>You can specify
+<section id="referactions"><title>Referential actions</title>
+<indexterm>Referential actions</indexterm>
+<indexterm>CONSTRAINT clause<indexterm>referential actions in</indexterm></indexterm>
+<p>You can specify
an ON DELETE clause and/or an ON UPDATE clause, followed by the appropriate
action (CASCADE, RESTRICT, SET NULL, or NO ACTION) when defining foreign keys.
These clauses specify whether <ph conref="../conrefs.dita#prod/productshortname"></ph> should
@@ -260,7 +290,19 @@ operation. </li>
when a parent table is the object of a delete. </li>
<li>If the dependent table is also a parent table, the actions described in
this list apply, in turn, to its dependents.</li>
-</ul></p> </section>
+</ul></p>
+<p>If a foreign key's constraint mode is DEFERRED, an insert (or update of a row
+that changes the foreign key) in the child table will be checked at deferred
+checking time, notwithstanding the ON DELETE or ON UPDATE referential action
+specification. If a row in the parent table is deleted (or updated so as to
+modify the referenced key), the behavior depends on the specification of
+ON DELETE or ON UPDATE. Only if NO ACTION has been specified is the checking
+ever deferred. If the primary table's referenced primary or unique key
+constraint is also deferred, any delete of a parent row can lead to a foreign
+key violation immediately (or at deferred checking time, if the foreign key is
+also deferred, as the case may be) when the last of possibly several key
+duplicates of the referenced key is deleted or updated.</p>
+</section>
<section><title>Statement dependency system</title> <p>INSERT
and UPDATE statements depend on all constraints on the target table. DELETEs
depend on unique, primary key, and foreign key constraints. These statements
@@ -293,7 +335,7 @@ CREATE TABLE SAMP.EMP
(
EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
FIRSTNME CHAR(12) NOT NULL,
- MIDINIT vARCHAR(12) NOT NULL,
+ MIDINIT VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
BONUS DECIMAL(9,2),
@@ -316,6 +358,22 @@ CREATE TABLE FLIGHTS
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
);
+-- use the same check constraint, but
+-- make the MEAL_CONSTRAINT deferrable
+CREATE TABLE FLIGHTS
+ (
+ FLIGHT_ID CHAR(6) NOT NULL,
+ SEGMENT_NUMBER INTEGER NOT NULL,
+ ORIG_AIRPORT CHAR(3),
+ DEPART_TIME TIME,
+ DEST_AIRPORT CHAR(3),
+ ARRIVE_TIME TIME,
+ MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
+ CHECK (MEAL IN ('B', 'L', 'D', 'S'))
+ DEFERRABLE INITIALLY DEFERRED,
+ PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
+ );
+
CREATE TABLE METROPOLITAN
(
HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
Modified: db/derby/docs/trunk/src/ref/rrefsqlj16095.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj16095.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj16095.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj16095.dita Thu May 29 19:04:11 2014
@@ -28,7 +28,7 @@ limitations under the License.
PRIMARY KEY |
UNIQUE |
<i><xref href="rrefsqlj16357.dita#rrefsqlj16357">REFERENCES clause</xref></i>
-}</b></codeblock></example>
+} [ <i><xref href="rrefsqljconstrchar.dita">constraintCharacteristics</xref></i> ]</b></codeblock></example>
<section><p>A <i>searchCondition</i> is any
<xref href="rrefsqlj23075.dita#rrefsqlj23075">boolean expression</xref> that
meets the requirements specified in
Modified: db/derby/docs/trunk/src/ref/rrefsqlj42154.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj42154.dita?rev=1598372&r1=1598371&r2=1598372&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj42154.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj42154.dita Thu May 29 19:04:11 2014
@@ -33,7 +33,7 @@ href="rrefsimplecolumnname.dita#rrefsimp
href="rrefsimplecolumnname.dita#rrefsimplecolumnname">simpleColumnName</xref></i> ]* )
<i> <xref href="rrefsqlj16357.dita#rrefsqlj16357">REFERENCES clause</xref></i>
}
-}</b></codeblock></example>
+} [ <i><xref href="rrefsqljconstrchar.dita">constraintCharacteristics</xref></i> ]</b></codeblock></example>
<section><p>A <i>searchCondition</i> is any
<xref href="rrefsqlj23075.dita#rrefsqlj23075">boolean expression</xref> that
meets the requirements specified in
Added: db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita?rev=1598372&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita Thu May 29 19:04:11 2014
@@ -0,0 +1,61 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rrefsqljconstrchar" xml:lang="en-us">
+<title>constraintCharacteristics</title>
+<prolog><metadata>
+<keywords>
+<indexterm>deferrable constraints</indexterm>
+<indexterm>constraints<indexterm>deferrable</indexterm></indexterm>
+</keywords>
+</metadata></prolog>
+<refbody>
+<example>
+<codeblock><b><i>constraintCheckTime</i> [ [ NOT ] DEFERRABLE ] |
+[ NOT ] DEFERRABLE [ <i>constraintCheckTime</i> ]</b></codeblock>
+</example>
+<section><p>The <i>constraintCheckTime</i> is defined as follows:</p>
+<codeblock><b>INITIALLY DEFERRED | INITIALLY IMMEDIATE</b></codeblock>
+<p>If DEFERRABLE is specified, the constraint is deferrable; otherwise it is not
+deferrable unless INITIALLY DEFERRED is specified. To make a constraint from an
+existing database deferrable, you must drop and recreate the constraint.</p>
+<p>If <i>constraintCheckTime</i> is not specified, INITIALLY IMMEDIATE is
+implicit.</p>
+<p>If INITIALLY DEFERRED is specified and DEFERRABLE is not specified,
+DEFERRABLE is implicit. If INITIALLY DEFERRED is specified, NOT DEFERRABLE is
+not permitted.</p>
+<p>The deferrability or the <i>constraintCheckTime</i> (that is, the default
+checking time) of a constraint cannot be altered. To change these
+characteristics, you must drop the constraint and recreate it.</p>
+<p>NOT NULL constraints are not deferrable; all others are deferrable.
+The NOT NULL constraint can, however, be dropped and recreated if desired. This
+will require a full table scan.</p>
+<note>Deferred constraints sometimes impose extra performance overhead to allow
+for the deferred checking. If your application does not require deferred
+checking, we recommend that you make constraints NOT DEFERRABLE (the
+default).</note>
+<note>In contrast to constraint checking, the referential actions specified by a
+referential constraint are never deferred. In
+<ph conref="../conrefs.dita#prod/productshortname"></ph>, these actions are
+RESTRICT, SET NULL and CASCADE for delete and RESTRICT for update. If NO ACTION
+is specified, the referential check can be deferred.</note>
+</section>
+</refbody>
+</reference>
Propchange: db/derby/docs/trunk/src/ref/rrefsqljconstrchar.dita
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita?rev=1598372&view=auto
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita (added)
+++ db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita Thu May 29 19:04:11 2014
@@ -0,0 +1,82 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+-->
+
+<!DOCTYPE reference PUBLIC "-//OASIS//DTD DITA Reference//EN"
+ "../dtd/reference.dtd">
+<reference id="rrefsqljsetconstr" xml:lang="en-us">
+<title>SET CONSTRAINTS statement</title>
+<shortdesc>The SET CONSTRAINTS statement sets the deferrability of one or
+more constraints.</shortdesc>
+<prolog><metadata>
+<keywords>
+<indexterm>SET CONSTRAINTS statement</indexterm>
+<indexterm>SQL statements<indexterm>SET CONSTRAINTS</indexterm></indexterm>
+<indexterm>constraints<indexterm>setting deferrability</indexterm></indexterm>
+</keywords>
+</metadata></prolog>
+<refbody>
+<section>
+<p>The SET CONSTRAINTS statement allows you to set the constraint mode for one
+or more constraints either to DEFERRED or to IMMEDIATE.</p>
+<p>When you use the statement to change a constraint from DEFERRED to
+IMMEDIATE, the constraint is checked as soon as the statement is executed.</p>
+<p>If the check fails, the transaction is not rolled back; an error here
+constitutes a statement level error only. Therefore, you can use this statement
+to check if all constraints are fulfilled before you attempt to commit the
+transaction.</p>
+<p>For more information on deferrable constraints, see
+<xref href="rrefsqlj13590.dita"/> and
+<xref href="rrefsqljconstrchar.dita"/>.</p>
+<p>It is recommended that you use SET CONSTRAINTS on
+<xref href="rrefsqlj42154.dita">table-level constraints</xref>. If you use
+SET CONSTRAINTS on a <xref href="rrefsqlj16095.dita">column-level
+constraint</xref>, you will need to find the name of the corresponding index by
+performing queries against the system tables, which is cumbersome and requires
+additional non-portable SQL.</p>
+</section>
+<refsyn><title>Syntax</title>
+<codeblock><b>SET CONSTRAINTS <i>constraintNameList</i> { DEFERRED | IMMEDIATE }</b></codeblock>
+<p>The <i>constraintNameList</i> is defined as follows:</p>
+<codeblock><b>ALL | <i><xref href="rrefconstraintname.dita">constraintName</xref></i> [ { , <i>constraintName</i> }... ]</b></codeblock>
+</refsyn>
+<section><title>Runtime behavior</title>
+<p>If the constraint mode is DEFERRED and a violation is seen at commit time,
+an exception is thrown, and the transaction is rolled back.</p>
+<p>When you change the constraint mode explicitly to IMMEDIATE using
+SET CONSTRAINTS, the constraint is checked, but slightly differently from the
+way it is checked at commit time: if a violation is found, a statement-level
+exception is thrown. You can use this behavior to verify that constraints are
+fulfilled before you attempt to commit.</p>
+<p>If the constraint mode is IMMEDIATE upon entering a stored routine, and that
+routine in a nested connection changes the constraint mode to DEFERRED, any
+constraints that are affected are checked upon return from the routine. If the
+check fails, an exception is thrown, and the transaction is rolled back.</p>
+<p>Constraints with a constraint mode of DEFERRED are also checked if the
+application calls <i>XAResource.prepare(Xid)</i>. If there is a violation,
+<ph conref="../conrefs.dita#prod/productshortname"></ph> throws
+<i>XAException.XA_RBINTEGRITY</i>, and the XA transaction is rolled back.</p>
+</section>
+<example><title>Examples</title>
+<codeblock><b>SET CONSTRAINTS FOO DEFERRED;
+
+SET CONSTRAINTS ALL DEFERRED;
+
+SET CONSTRAINTS FOO, BAR IMMEDIATE;</b></codeblock>
+</example>
+</refbody>
+</reference>
Propchange: db/derby/docs/trunk/src/ref/rrefsqljsetconstr.dita
------------------------------------------------------------------------------
svn:eol-style = native