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

Question

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)

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.

score 3 · Accepted Answer · answered Feb 14 '14 at 16:19

3

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.

answered Feb 14 '14 at 16:19

Alex Poole

183,384
11
179
318

I use deferred constrains when 2 tables need to FK to one another. It is a very TIGHT fk constraint. I've used it a few times...hardly ever. – Brian McGinity Feb 14 '14 at 18:35
@BrianMcGinity - it's the use of deferred *check* constraints that I'm puzzled by, not deferral in general. I think I've only ever seen FKs deferred. – Alex Poole Feb 14 '14 at 18:40
Oh...that is weird...I can't think of another reason than what you've outlined. If you do not know a value at insert then do something to find the value then update the table before commit... hmmm...can't think of another reason... – Brian McGinity Feb 14 '14 at 18:48
Sometimes, one inherits really bad schemas and there arises a desire to "rein them in" a little... For example, "polymorphic associations" and similar things... http://dba.stackexchange.com/q/58308/33863 – starlocke Feb 14 '14 at 21:44

score 1 · Answer 2 · answered Feb 14 '14 at 16:16

Yes, you can define Constraint as

"DEFERRABLE" or "NOT DEFERRABLE"

and then

"INITIALLY DEFERRED" or "INITIALLY IMMEDIATE"

For example:

ALTER TABLE T
ADD CONSTRAINT ck_t CHECK (COL_1 > 0)
DEFERRABLE INITIALLY DEFERRED;

Check Oracle documentation for details...

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

2 Answers2

Linked