NOVALIDATE Constraints – No really …

There have been a number of posts recently on the OTN database forum regarding the whole topic of NOVALIDATE of constraints and the associated indexes so I thought it might be worth going over a couple of interesting little quirks with all this.

A NOVALIDATE constraint is basically a constraint which can be enabled but for which Oracle will not check the existing data to determine whether there might be data that currently violates the constraint.

This is useful if we know there’s data that violates the constraint but we want to quickly put on a constraint to prevent further violations, with the intention to clean up any possible violations at some future point in time.

It’s also potentially useful if we know the data is clean and so want to prevent the potentially significant overheads of Oracle having to check all the data to ensure there are indeed no violations.

I previously discussed the use of Non-Unique Indexes for manageing Primary and Unique Key Constraints but there are a few little traps one can easily fall into if one doesn’t understand these two very important fundamentals:

1. By default, Oracle will attempt to create a Unique Index to police a PK or UK constraint
2. A NOVALIDATE constraint requires a Non-Unique Index for the constraint to really be “Novalidated”

Get these two concepts confused and things can easily get a little difficult to follow …

Here’s a little example of how things can start to get confusing. First, let’s create a simple little table and populate it with a few rows.

SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

Note that the ID column is populated with unique values. However, let’s now introduce a duplicate value, 42:

SQL> INSERT INTO ziggy VALUES (42, ‘DUPLICATE’);

1 row created.

SQL> COMMIT;

Commit complete.

OK, we now want to add a Primary Key to this table but because we suspect there might be some duplicate values which we intend to clean up at some future point in time, we want to create the constraint with NOVALIDATE:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;
ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE
                                 *
ERROR at line 1:
ORA-02437: cannot validate (BOWIE.ZIGGY_PK) – primary key violated

Now what the hell is going on here ?

We clearly stated we want to create a NOVALIDATE constraint but Oracle appears to be ignoring this and is validating the constraint regardless and so generating an error because of the duplicate entry.

Why ?

Because by default Oracle will attempt to create a Unique index when creating a PK constraint. A Unique index MUST always contain unique values and so complains when it stumbles across our duplicate 42 ID value. The constraint is being effectively validated because the unique index will only be created providing there are indeed no duplicate values.

Not how I would have designed things but there you go …

However, if we either have an existing Non-Unique index which Oracle can use or we explicitly create a Non-Unique index, then we can proceed with creating the NOVALIDATE constraint as required:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id)) ENABLE NOVALIDATE;

Table altered.

If we look at the status of the constraint and the type of index used to police the constraint, we notice that the index is indeed a Non-Unique index and the constraint has not been validated:

SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;

CONSTRAINT_NAME VALIDATED     UNIQUENES
--------------- ------------- ---------
ZIGGY_PK        NOT VALIDATED NONUNIQUE

We have a PK constraint even though there are currently duplicate values of the PK column in the data.

OK, let’s now drop and the constraint, the Unique Index and delete the duplicate row:

SQL> ALTER TABLE ziggy DROP PRIMARY KEY;

Table altered.

SQL> DROP INDEX ZIGGY_PK;

Index dropped.

SQL> DELETE ziggy WHERE id = 42 and rownum <= 1;

1 row deleted.

SQL> COMMIT;

Commit complete.

The data is now clean and we have no existing constraint or index on the ID column:

SQL> SELECT constraint_name, validated FROM user_constraints WHERE table_name= ‘ZIGGY’;

no rows selected

Let’s now do something that based on our understanding might appear to be a little odd, let’s try and recreate the constraint in a NOVALIDATE state but with a Unique index. This of course should now work as there are indeed no duplicates within the data:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;

Table altered.

Success !! Let’s now look at the state of the constraint and the type of index created:

SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;

CONSTRAINT_NAME VALIDATED     UNIQUENES
--------------- ------------- ---------
ZIGGY_PK        NOT VALIDATED UNIQUE 

As expected, we have a constraint that’s policed by a Unique index that has not been validated.

This might appear be a little odd, because the question you might well now ask is why bother no validating a constraint that has effectively been validated anyways as the use of the Unique index has guaranteed there can not possibly be any duplicate values else the creation of the Unique index would have failed ?

We effectively have a validated constraint which Oracle is still classifying as being not validated 🙂

Then again, maybe not …

More later.