Re: about constraint statuses

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 15 Aug 2001 21:22:37 +1000
Message-ID: <3b7a5ab7@usenet.per.paradox.net.au>

"Dino Hsu" <dino1_nospam_at_ms1.hinet.net> wrote in message news:dbtgntkattr275et9lgsfi778kb4s4p0bq_at_4ax.com...
> Dear all,
>
> Quoting a book:
> "Constraint Statuses:
> ... <snip, about enable validate and enable novalidate>
> Also, Oracle can support UNIQUE constraints being enforced with
> nonunique indexes. The columns indexed as part of the UNIQUE
> constraint should be the first columns in the nonunique index, but as
> long as those columns are the leading columns of the index, they may
> appear in any order. Other columns can also be present in the index to
> make it nonunique. This feature speeds the process of enabling PRIMARY
> KEY or UNIQUE constraints on the table. The nonunique index supporting
> the UNIQUE or PRIMARY KEY constraint cannot be dropped.
> In Oracle8i, there is a fourth status for integrity constraint called
> DISABLE VALIDATE. If a constraint is in this state, any modification
> of the constrained columns is not allowed. In addtion, the index on
> the constraint is dropped and the constraint is disabled. That is
> useful for a UNIQUE constraint, the DISABLE VALIDATE state enables you
> to load data efficiently from a nonpartitioned table into a
> partitioned table using the exchange partition option of the alter
> table command."
>
> What is "UNIQUE constraints being enforced with nonunique index"? How
> can they "speed the process of enabling PRIMARY KEY or UNIQUE
> constraints on the table"?

This has just recently been asked. If you create a table with no constraints, and then create your own index -manually- on a variety of columns, including what is logically the primary key column as its leading field, then when you later 'alter table add constraint', you can use the 'using index' clause to point to the existing index. Even though it is non-unique, Oracle will be able to use it.

And because the index has already been created, the time taken to create the constraint is much reduced -because normally, defining a primary key or unique constraint would require an index (of whatever sort of uniqueness or non-uniqueness) to be built *at the time of constraint definition*.

Alternatively, defining the constraint to one day be 'deferrable' (even if it is initially immediate) will cause that automatically-created index to be created as a non-unique index.

Hence:

Create table pk_test (
col1 number constraint pktest_col1_pk primary key deferrable

        initially immediate
        using index
        tablespace indx01,

col2 char(3))
tablespace data01;

... will cause a non-unique index to be generated on the col1 column. And it will enforce primary-keyness just as well and just as efficiently as a unique index would (which is what you would have got created if you'd left out the keyword 'deferrable').

>I know that PRIMARY KEY and UNIQUE
> constraints are accompanied with indexes implicitly, so these
> constraints somhow behave similarly to indexes, but I don't see how
> UNIQUE constraints correspond to nonunique indexes.
>
> Moreover, I don't understand how DISABLE VALIDATE can be useful. Hope
> someone can help me to conquer this. Thanks in advance.
>

Disable Validate means that even though the constraint is disabled, we can be sure that the records in the table do not violate the integrity constraint rules -by the simple expedient of locking the table from all updates or inserts that would affect the constrained columns.

Why are they useful? Because an index that is used to enforce a primary key or unique constraint cannot be dropped, unless you first disable the constraint (and, incidentally, disabling such a constraint when enforced with a unique index causes the index to be dropped automatically).

So, say you want to drop an index so that you can perform maintenance on the tablespace ... but you can't drop the index unless the constraint is first disabled ... but disabling the constraint would mean that any User could do all osrts of damaging things to the data in the table during the non-constrained period. What you want is a means of disabling the constraint that still leaves the table in a guaranteed clean state... and that's exactly what 'disabled but validated' does for you.

It's particularly useful when exchanging partitions, because exchanging sometimes means having to drop and recreate indexes -but dropping the index on one partition (possible only if you disable the constraint) would allow all sorts of muck to get into all the other partitions, if Users so chose. With disabled novalidate you can be sure that no damage to the other partitions takes place whilst you are fiddling around with the new partition.

Regards
HJR
> Dino
>
Received on Wed Aug 15 2001 - 06:22:37 CDT