Re: Any experts on constraint deferrability?

"Chris O" <itoys1 -a t- optusnet -d o t- com -d o t- au> 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]?
>
>2. Has anyone out there ever had reason to defer a NOT-NULL constraint?
>
>Cheers
>
>

Theory only, never used it myself:

1. It appears that the NOT NULL clause in the Create statement is just 'shorthand' for creating a Check constraint and the server's parser uses the 'actual' code not the shorthand..
2. I can imagine a case where a table is updated from more than one source and at different times ( say, sequentially) so that the first pass inserts records that do not have a value for that column, so the constraint is deferred until the second pass which updates that column ( and maybe others ) and it is at that point you need to insure the NOT NULL is enforced.. Just a thought..