oracleconstraintscheck-constraints

Is it possible to have a "deferred check constraint" in Oracle?


I was thinking that I'd like to have a "deferred check constraint" in Postgres, but that is apparently not supported at this time (Postgres 9.3)

NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement).

Then I saw that Oracle seems to broadly have "deferred" for its constraints, documented here. Therefore, is it true that Oracle 10g+ supports having a "deferred check constraint"?

I might have missed further documentation to the contrary, so I figured to ask here as a double-check, trusting that there are people who actively use Oracle who would know the answer - thus avoiding trial-and-error, wasted hours messing around with Oracle servers.


Solution

  • Yes, though I'm not sure why you'd want to:

    create table t42 (id number,
      constraint check_id check (id > 0) initially deferred deferrable);
    
    table T42 created.
    
    insert into t42 (id) values (-1);
    
    1 rows inserted.
    
    commit;
    
    Error report -
    SQL Error: ORA-02091: transaction rolled back
    ORA-02290: check constraint (STACKOVERFLOW.CHECK_ID) violated
    02091. 00000 -  "transaction rolled back"
    *Cause:    Also see error 2092. If the transaction is aborted at a remote
               site then you will only see 2091; if aborted at host then you will
               see 2092 and 2091.
    *Action:   Add rollback segment and retry the transaction.
    

    You can update it before committing of course:

    insert into t42 (id) values (-1);
    
    1 rows inserted.
    
    update t42 set id = 1 where id = -1;
    
    1 rows updated.
    
    commit;
    
    committed.
    

    ... but I'm not sure why you would put the invalid value in the table in the first place if you planned to update it. Presumably there is some scenario where this is useful.

    More on constraint deferral in the documentation.