Re: Any experts on constraint deferrability?

"Quarkman" <quarkman_at_myrealbox.com> wrote in message news:oprsog01x0r9lm4d_at_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
>
>

Hi Guys. Thanks for the responses. QM, I guess I'm struggling to see your example
as being a legitimate use of deferring not-null constraints. I admit I've been out of the applications
area for quite some time now.

All in all, I am still left wondering whether anyone actaully has a valid use for deferring not-null
constraints. The ANSI SQL92 standard allows it, Oracle supports it, so I guess these guys
know what they are doing.

As for point 1, the Oracle server DOES distinguish NOT-NULL constraints from CHECK constraints. Although it presents them both as type 'C' [i.e. CHECK] in the
DBA_CONSTRAINTS view, if you look in the underlying base catalog tables [in particular the TYPE# column of the table SYS.CDEF$] you wiill see that NOT-NULL constraints have a code of 7 whereas CHECK constraints have
code of 1. Furthermore, you can only add one NOT-NULL constraint to a column but
can add as many CHECK constraints of the form CHECK("column-name" IS NOT NULL)
as you want. Since the Oracle server DOES dintinguish the two types [at least internally],
this is why I was curious why Oracle had transformed the NOT-NULL definition into
a CHECK constraint definition.

Chris O Received on Mon Jul 21 2003 - 18:06:52 CDT