Jasen Betts wrote:
> Well, the standard syntax allows them to be requested, check constraints too.
>
> what does the standard say about it behaviourally?
What you'd expect:
The checking of a constraint depends on its constraint mode
within the current SQL-transaction. If the constraint
mode is immediate, then the constraint is effectively
checked at the end of each SQL-statement.
If the constraint mode is deferred, then the constraint
is effectively checked when the constraint mode is changed
to immediate either explicitly by execution of a
<set constraints mode statement>, or implicitly at the end of
the current SQL-transaction.
When a constraint is checked other than at the end of an
SQL-transaction, if it is not satisfied, then an exception
condition is raised and the SQL-statement that caused the
constraint to be checked has no effect other than
entering the exception information into the first diagnostics
area. When a <commit statement> is executed, all
constraints are effectively checked and, if any constraint
is not satisfied, then an exception condition is raised
and the SQL-transaction is terminated by an implicit
<rollback statement>.
> what do other major SQL databases do?
Seems to work in Oracle:
CREATE TABLE con_test(
ID NUMBER(5)
CONSTRAINT con_test_pk PRIMARY KEY DEFERRABLE INITIALLY DEFERRED,
val VARCHAR2(20 CHAR)
CONSTRAINT con_test_val_null NOT NULL DEFERRABLE INITIALLY DEFERRED
);
Table created.
INSERT INTO con_test VALUES (1, NULL);
1 row created.
UPDATE con_test SET val = 'one' WHERE id = 1;
1 row updated.
COMMIT;
Commit complete.
INSERT INTO con_test VALUES (1, 'two');
1 row created.
UPDATE con_test SET id = 2 WHERE val = 'two';
1 row updated.
COMMIT;
Commit complete.
INSERT INTO con_test VALUES (1, 'three');
1 row created.
COMMIT;
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (LAURENZ.CON_TEST_PK) violated
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
