Re: DEFERRABLE NOT NULL constraint

From: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
To: "Jasen Betts *EXTERN*" <jasen(at)xnet(dot)co(dot)nz>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: DEFERRABLE NOT NULL constraint
Date: 2013-02-07 14:42:38
Message-ID: A737B7A37273E048B164557ADEF4A58B057B0D71@ntex2010a.host.magwien.gv.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Anoop K 2013-02-07 14:49:22 Re: REINDEX deadlock - Postgresql -9.1
Previous Message Kevin Grittner 2013-02-07 14:12:44 Re: REINDEX deadlock - Postgresql -9.1