Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore
    Posts
    6

    Unanswered: Oracle Disable Novalidate And Disable Validate

    hi...


    in what case or scenario these states are useful..

    DISABLE VALIDATE : if it has to validate the column even after disabling why it has to be disabled...


    thanx in advance..
    sarika

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Re: Oracle Disable Novalidate And Disable Validate

    Originally posted by sarika_puranik
    hi...


    in what case or scenario these states are useful..

    DISABLE VALIDATE : if it has to validate the column even after disabling why it has to be disabled...


    thanx in advance..
    sarika
    Hi Sarika

    In table constraint can be enable & disabled using create and alter statment. In addition VAILDATE and NOVALIDATE keywords can used to perform the action of the state.

    In DETAILS Plz. follow the URL:

    http://www.oracle-base.com/articles/...ingUpdates.php

  3. #3
    Join Date
    Feb 2007
    Posts
    71
    hi this might be an old topic but I am a bit confused

    when we say
    DISABLE NOVALIDATE - incoming and existing data in the table might not conform to business rules
    DISABLE VALIDATE - incoming data are not checked but Oracle locks the table for validation


    I've got a case here wherein I received the error:
    ERROR at line 1:
    ORA-25128: No insert/update/delete on table with constraint
    (OWNER.PK_CUSTOMERCODE) disabled and validated

    I want to insert a record into customers table
    INSERT INTO CUSTOMERS VALUES (1,'GARD','ANTHONY');

    the structure of the table is
    CUSTOMERCODE NUMERIC PK (PK_CUSTOMERCODE is the constraint name)
    FIRSTNAME VARCHAR2(50)
    LASTNAME VARCHAR2(50)

    PK_CUSTOMERCODE constraint is classified under DEFERRABLE IMMEDIATE DEFERRED category

    Currently, the table contains one record
    1 JOSEPH GONDOR

    I tried to issued the following SQL statements
    ALTER TABLE CUSTOMERS
    DISABLE NOVALIDATE CONSTRAINT PK_CUSTOMERCODE;
    INSERT INTO CUSTOMERS VALUES (1,'GARD','ANTHONY');
    COMMIT;

    1 row created.

    I changed the state to
    DISABLE VALIDATE
    truncated the table
    executed one DML
    INSERT INTO CUSTOMERS VALUES (1,'GARD','ANTHONY');

    and got the error mentioned above,

    strange, isn't it that DISABLE VALIDATE will only accept incoming DML(and not check it) but will check the existing data for consistency?Since the table was already truncated, are there anymore rules that the insert statement have violated? thus hindering it from inserting a record?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •