Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unhappy Unanswered: Enable Novalidate !!!

    Enable Novalidate does not work with Primary Key and Unique constraints !!!!!

    SQL> create table X(Y number primary key);

    Table created.

    SQL> alter table X disable primary key;

    Table altered.

    SQL> insert into X values(1);

    1 row created.

    SQL> insert into X values(1);

    1 row created.

    SQL> alter table X enable novalidate primary key;
    alter table X enable novalidate primary key
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.SYS_C00646221) - primary key violated

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Enable Novalidate !!!

    There is a way (though I hope this is a purely academic exercise: a table with duplicate values in the primary key column is an abomination!):

    SQL> create table X(Y number primary key deferrable initially immediate);

    Table created.

    SQL> alter table X disable primary key;

    Table altered.

    SQL> insert into X values(1);

    1 row created.

    SQL> insert into X values(1);

    1 row created.

    SQL> alter table X enable novalidate primary key;

    Table altered.

    SQL> insert into X values(1);
    insert into X values(1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.SYS_C003079) violated

    SQL> select * from x;

    Y
    ----------
    1
    1

    It works because Oracle creates a NONUNIQUE index when the primary key is deferrable; otherwise it creates a UNIQUE index.

  3. #3
    Join Date
    Dec 2002
    Posts
    5
    Thanks Tony!
    Yes it seems like we need a non-unique index, either by way of deferrable or by first creating one before creating the primary key. But I have never seen these two getting linked in any books. This forum is great!

Posting Permissions

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