#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2005
    Posts
    15
    Rep Power
    0

    integrity constraint: DISABLE VALIDATE


    in oracle 9i documentation i have read that DISABLE clause can be:
    DISABLE NOVALIDATE (default. i understand vat it means) or
    DISABLE VALIDATE:
    disables the constraint and drops the index on the
    constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, because it lets you load large amounts of data while also saving space by not having an index. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.

    question1 if this means that data which violates the constraint is not allowed in the table, then vat is the difference between disable validate and an enabled constraint?
    question2 is this only a 9i feature or is it available in oracle8 as well? because i dont find it in my oracle8 book (which we r following in school)
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    In short:
    ENABLE ensures that all incoming data conforms to the constraint
    DISABLE allows incoming data, regardless of whether it conforms to the
    constraint
    VALIDATE ensures that existing data conforms to the constraint
    NOVALIDATE means that some existing data may not conform to the constraint
    ...
    DISABLE VALIDATE disables the constraint, drops the index on the constraint,
    and disallows any modification of the constrained columns.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2005
    Posts
    15
    Rep Power
    0
    Originally Posted by cherry1
    DISABLE VALIDATE:
    disables the constraint and drops the index on the
    constraint, but keeps the constraint valid.

    question2 is this only a 9i feature or is it available in oracle8 as well? because i dont find it in my oracle8 book (which we r following in school)
    im sorry i must be particularly dense coz i still dont get it. vat does it mean by keeping the constraint valid?
    also, is this only a 9i feature?
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    question 1:
    DISABLE VALIDATE disables the constraint, drops the index on the constraint,
    and disallows any modification of the constrained columns.
    It basically means that the index used to enforce the constraint is dropped and you can not modify the table data.
    It's used to freeze the table for:
    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.
    as you can read here which is also an example of the official docs coming with Oracle.
    question2:
    No, the feature is available in 8i also.
    Last edited by pabloj; April 28th, 2005 at 03:24 AM.

IMN logo majestic logo threadwatch logo seochat tools logo