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
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 |