Skip to Main Content
  • Questions

  • Enable Novalidate does not work for PK?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yong.

Asked: August 09, 2000 - 10:42 am UTC

Last updated: November 01, 2005 - 3:17 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

I know NOVALIDATE works for foreign keys. But it doesn't seem to work for primary keys:

SQL> create table q (a number, constraint pk_q primary key (a));

Table created.

SQL> alter table q disable constraint pk_q;

Table altered.

SQL> insert into q values (1);

1 row created.

SQL> insert into q values (1);

1 row created.

SQL> alter table q modify constraint pk_q enable novalidate;
alter table q modify constraint pk_q enable novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (INDIGO2.PK_Q) - primary key violated


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


and Tom said...

You can do this with a deferrable constraint. These are enforced with non-unique indexes. They are not the default (so by default -- unique index).

It would look like:


ops$tkyte@8i> create table t
2 ( x int,
3 constraint t_pk primary key (x)
4 deferrable initially immediate );

Table created.

ops$tkyte@8i>
ops$tkyte@8i> alter table t disable constraint t_pk;

Table altered.

ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1 );

1 row created.

ops$tkyte@8i> insert into t values ( 1);

1 row created.

ops$tkyte@8i>
ops$tkyte@8i> alter table t modify constraint t_pk enable novalidate
2 /

Table altered.

ops$tkyte@8i>
ops$tkyte@8i> insert into t values ( 1);
insert into t values ( 1)
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_PK) violated


ops$tkyte@8i>

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

...or pre-create the index on pk as non-unique

Nj?l A. Ekern, October 30, 2001 - 6:53 am UTC 

Very appliciable that Oracle creates non-unique indexes for deferrable unique constraints.

Another way to do this is to pre-create a non-unique index on the primary key. The creation of the primary key will not create an index automatically if a similar index (unique or non-unique) already exists on the pk-columns:

SQL> create table q (a number);

Table created.

-- Now, create a non-unique index:

SQL> create index pk_q on q(a);

Index created.

SQL> alter table q add(constraint pk_q primary key (a));

Table altered.

SQL> alter table q disable constraint pk_q;

Table altered.

SQL> insert into q values (1);

1 row created.

SQL> insert into q values (1);

1 row created.

SQL> alter table q modify constraint pk_q enable novalidate;

Table altered.

SQL> insert into q values (1);
insert into q values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (xxx.PK_Q) violated

SQL> insert into q values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from q;
         1
         1
         2

3 rows selected.

Puneet Mathur, January 06, 2004 - 7:02 am UTC

Excellent search was excatly what i wanted

Primary Key and Non-Unique Index

Reviewer, February 13, 2004 - 1:03 pm UTC

Suppose a primary key is defined as Deferrable and Initially immediate. Then a non-unique index is created for the primary key.

Then how the uniqueness of the Primary key column is checked. I have read that Primary key is implemented by using a Unique Index.

Tom Kyte
February 13, 2004 - 1:25 pm UTC

You read something that is not correct.

Since we implemented deferrable constraints, all we need is an index -- unique or otherwise.

"how" -- Oracle does it. It uses an index, but it does it. Index need not be unique. It just "works"

I have a table like:

PM, May 29, 2004 - 6:29 am UTC

Extremely bad the reviewers and follow uppers are not aware of what they are talking about

Tom Kyte
May 29, 2004 - 11:27 am UTC

er? having a bad day PM? no clue what you are trying to convey.

Disabled constraint getting violated

Ajay, October 31, 2005 - 6:47 am UTC 

I have disabled a constraint on a table as seen below:-

SELECT OWNER, CONSTRAINT_NAME , CONSTRAINT_TYPE ,
TABLE_NAME, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED
FROM USER_CONSTRAINTS WHERE TABLE_NAME='AUTH_USER'
AND CONSTRAINT_NAME = 'XPKAUTH_USER'
/

OWNER                          CONSTRAINT_NAME
C TABLE_NAME                     STATUS   DEFERRABLE     DEFERRED
VALIDATED     GENERATED
        ------------------------------
------------------------------ - ------------------------------ --------
-------------- --------- ------------- --------------
ORDER_SCHEMA                   XPKAUTH_USER
P AUTH_USER                      DISABLED NOT DEFERRABLE IMMEDIATE NOT
VALIDATED USER NAME

However, when inserting data into the table, I am getting message for
the same constraint getting violated.
Why is this happening?

        SQL> insert into AUTH_USER
          2  (
          3   user_id,
          4   status,
          5   nt_auth_id,
          6   forms_auth_id,
          7   create_date,
          8   modify_date,
          9   update_by,
         10   update_system,
         11   update_db_username,
         12   UPDATE_HOSTNAME,
         13   UPDATE_OSUSER  
         14  )
         15  select
         16   user_id,
         17   status,
         18   nt_auth_id,
         19   forms_auth_id,
         20   create_date,
         21   modify_date,
         22   update_by,
         23   update_system,
         24   update_db_username,
         25   UPDATE_HOSTNAME,
         26   UPDATE_OSUSER  
         27  from order_schema.AUTH_USER@obcrpprod_node1.world;
        insert into AUTH_USER
        *
        ERROR at line 1:
        ORA-00001: unique constraint (ORDER_SCHEMA.XPKAUTH_USER)
violated

Tom Kyte
November 01, 2005 - 3:17 am UTC 

you disabled the constraint, but you still have a unique index in place:


ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter table t add constraint t_unique unique(x);

Table altered.

ops$tkyte@ORA10GR2> alter table t disable constraint t_unique keep index;

Table altered.

ops$tkyte@ORA10GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_UNIQUE) violated


ops$tkyte@ORA10GR2> drop index t_unique;

Index dropped.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

A reader, November 21, 2005 - 9:42 am UTC


Spot on examples

Pauld, April 19, 2006 - 8:14 pm UTC

Straight to the point - clear and concise - thanks to everyone.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.