Re: [HACKERS] CONSTRAINTS... - Mailing list pgsql-hackers

>From "A Guide to The SQL standard" C.J.DATE:

FOREIGN KEY Syntax:

* base-table-constraint-def::= [ CONSTRAINT constraint ]    foreign-key-def [ deferrability ]
 foreign-key-def::= FOREIGN KEY ( column-commalist ) references-def
 references-def::= REFERENCES base-table [ ( column-commalist ) ]    [ MATCH { FULL | PARTIAL } ]    [ ON DELETE
referential-action]    [ ON UPDATE referential-action ]     referential-action::= NO ACTION | CASCADE | SET DEFAULT |
SETNULL
 
 deferrability::= INITIALLY { DEFERRED | IMMEDIATE } [ NOT ] DEFERRABLE


* column-constraint-def::= references-def [ deferrability ]


14.6 DEFERRED CONSTRAINT CHECKING 

Up to this point we have been assuming that all integrity constraints
are checked "immediately," i.e., as the final step in executing any SQL
statement - and, if any constraint is found to he violated, the
offending SQL statement is simply rejected, so that its overall effect
on the database is nil. Sometimes, however, it is necessary that certain
constraints not he checked until some later time, on the grounds that if
they were to be checked "immediately" they would always fail. Here is an
example (involving a referential cycle): 

* Suppose we have two base tables, Tl and T2, each of which includes a
foreign key that references some candidate key of the other, and suppose
we start with both tables empty. Then, if all foreign key checking is
done immediately, there is no way to get started: Any attempt to insert
a row into either table will fail, because there is no target row in the
other table that it can possibly reference.
The facilities described immediately following are intended to address
such situations. 
1. At any given time, with respect to any given transaction, any given
constraint must be in one or two "modes," immediate or deferred.*
Immediate means the constraint is checked "immediately" (as explained
above); deferred means it is not.
2. Any given constraint definition can optionally include either or both
of the following: 

INITIALLY { DEFERRED | IMMEDIATE }
[ NOT ] DEFERRABLE

These specifications appear as the final syntactic component of the
constraint definition. They can appear in either order.
- INITIALLY DEFERRED and NOT DEFERRABLE are mutually exclusive. If
neither INITIALLY DEFERRED nor INITIALLY IMMEDIATE is specified,
INITIALLY IMMEDIATE is implied. If INITIALLY IMMEDIATE is specified or
implied, then if neither DEFERRABLE nor NOT DEFERRABLE is specified, NOT
DEFERRABLE is implied. If lNITIALLY DEFERRED is specified, then (as
already explained) NOT DEFERRABLE must not he specified; DEFERRABLE can
be specified, but is implied anyway.
- INITIALLY DEFERRED and INITIALLY IMMEDIATE specify the "initial" mode
of the constraint   i.e., its mode immediately after it is defined and
at the start of every transaction'!   as deferred or immediate,
respectively. . DEFERRABLE and NOT DEFERRABLE specify whether or not
this constraint can ever be in deferred mode. DEFERRABLE means it can;
NOT DEFERRABLE means it cannot. 

3. The SET CONSTRAINTS statement is used to set the mode for specified
constraints with respect to the current transaction and current session
(or the next transaction to he initiated in the current session, if the
SQL-agent has no transaction currently executing). The syntax is: 

SET CONSTRAINTS { constraint-commalist | ALL }                 { DEFERRED | IMMEDIATE } 

Each "constraint" mentioned by name must he DEFERRABLE; ALL is short-
hand for "all DEFERRABLE constraints." If DEFERRED is specified, the
mode of all indicated constraints is set to deferred. If 1MMED1ATE is
specified, the mode of all indicated constraints is set to immediate,
and those constraints are then checked; if any check fails, the SET
CONSTRAINTS fails, and the mode of all indicated constraints remains
unchanged. Note that because of paragraph 4 below, the checks should not
fail if the SET CONSTRAINTS statement is executed while the SQL-agent
has no current transaction. 

4. COMMIT implies SET CONSTRAINTS ALL IMMEDIATE (for every active
SQL-session for the applicable SQL-transaction). If some implied
integrity check then fails, the COMMIT fails, and the transaction fails
also (i.e., is rolled back). 
To revert to the example mentioned at the beginning of this section (the
referential cycle involving two tables): We could deal with the problem
using the foregoing facilities as indicated by the following pseudocode.
Data definitions:
CREATE TABLE Tl CONSTRAINT T1FK FOREIGN KEY ... REFERENCES T2 
INITIALLY DEFERRED 

CREATE TABLE T2 CONSTRAINT T2FK FOREIGN KEY ... REFERENCES T1 
INITIALLY DEFERRED 

SQL-transaction: 

INSERT INTO T1 ( ... ) VALUES ( ... ) 
INSERT INTO T2 ( ... ) VALUES ( ... ) 
SET CONSTRAINTS T1FK, T2FK IMMEDIATE 
IF SQLSTATE = code meaning "SET CONSTRAINTS failed" 
THEN ROLLBACK  --cancel the INSERTs 




Jackson, DeJuan wrote:
> 
> So, could someone send me the SQL92 constraints syntax as well as the
> definition of what a deferrable constraint is supposed to be?
> ADVthanksANCE
>         -DEJ

-Jose'-