DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: novalidate not working

  1. #1
    Join Date
    Oct 2001
    Posts
    1
    I have a table where four fields will together form the primary key. However, the data thats already in there will violate the primary key, so I want to create it novalidate so the existing data can stay, yet any new data entered will be checked. However, I can't get novalidate to work. I made a copy of the table, deleted all the records from the original, created the constraint, disabled the constraint, copied the data from the temp table back into the original, and up to there everything is fine. Then, when I issue:
    alter table matdata enable novalidate constraint matdata_pk

    I get:
    SQL> alter table matdata enable novalidate constraint matdata_pk;
    alter table matdata enable novalidate constraint matdata_pk
    *
    ERROR at line 1:
    ORA-02437: cannot validate (TABS.MATDATA_PK) - primary key violated

    So, how is this even possible? What am I not getting? Oh, btw, I am using Oracle8i on Solaris 8.

    Thanks,
    D-J

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Did you create the PK with deferrable option?
    I know its not required for 'DISABLE/ ENABLE CONSTRAINT feature but in my opinion, the problem might be due to Unique index created by Oracle when you created the PK. If the constraint is created with deferable option, the associated index is non-unique and can store PK violators.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Check for any Unique index on any of the columns forming your PK with the following statement:

    Code:
    select c.column_name, i.uniqueness 
    from 
    user_ind_columns c, user_indexes i
    where
    c.table_name='METADATA'
    and
    c.Index_name=i.index_name
    /
    if there is some unique index, you will have to drop it before enabling constraint.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

Posting Permissions

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


Click Here to Expand Forum to Full Width