Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Any experts on constraint deferrability?

Re: Any experts on constraint deferrability?

From: Quarkman <quarkman_at_myrealbox.com>
Date: Tue, 22 Jul 2003 06:40:03 +1000
Message-ID: <oprsog01x0r9lm4d@haydn>


On Mon, 21 Jul 2003 22:28:13 +1000, Chris O" -a t-optusnet -d o t-com -d o t-au <itoys1> wrote:

> Hi All. This will seem like a wierd question but...
>
> In Oracle 8.1.7 [possibly earlier] through 9.2, if you declare this:
>
> CREATE TABLE x ( a NUMBER NOT NULL DEFERRABLE INITIALLY IMMEDIATE)
>
> the Oracle server turns it into:
>
> CREATE TABLE x ( a NUMBER CHECK ("A" IS NOT NULL) DEFERRABLE INITIALLY
> IMMEDIATE)
>
>
> 1. Does anyone know why they've done a transformation [other than someone
> in
> the kernel group doing a quick and dirty]?

Have a look at DBA_CONSTRAINTS, and specifically at the CONSTRAINT_TYPE column. You get "P" for Primary Key, "U" for Unique, "R" for Foreign Keys ('referential integrity'). And "C" for Not Nulls.

Hmmmm.... guess why you get "C" for Not Nulls (and always have had)? Because Not Nulls are considered a special form of check constraint (and always have been).

So this behaviour is normal. And documented.

> 2. Has anyone out there ever had reason to defer a NOT-NULL constraint?
>

Occasionally. Depends on the app. I've had an app which could only fill in one of the columns on a form to start with, but entries were eventually required for all of them. For business reasons, it was also a requirement that users not be allowed to start entering a form, and then back out. If the entry had to be cancelled because a customer refused to go through with the transaction, we nevertheless needed to know that they'd backed out. Therefore, the first entry in the form required a complete row to be inserted into a table (causing a sequence number to be assigned)... but clearly, at that stage, we didn't know what to fill in for the rest of the form at the point of insertion. So the constraints were made deferrable, permitting the insertion, but with an implied promise that we'd get round to filling in all the other fields ASAP.

It is a bit unusual, though, I would suggest. Much more common to see deferrable foreign key constraints (self-referencing).

~QM Received on Mon Jul 21 2003 - 15:40:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US