Re: DEFERRABLE NOT NULL constraint

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