jump to navigation

NOVALIDATE Constraints – No really … July 28, 2008

Posted by Richard Foote in Constraints, Indexing Tricks, Novalidate Constraints, Oracle Indexes, Primary Key, Unique Indexes.
trackback

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.

About these ads

Comments»

1. Amit - July 28, 2008

Hi richard,

I guess following line need to be corrected
“Because by default Oracle will attempt to create a Non-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.”

Shouldn’t this be “Because by default Oracle will attempt to create a Unique index when creating a PK constraint. “

2. Richard Foote - July 28, 2008

Hi Amit

Beat you to it. Already fixed.

Thanks

3. Brian Tkatch - July 28, 2008

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

Not really. The PK can still have NULL values:

SQL> CREATE TABLE A(A INT);

Table created.

SQL> INSERT INTO A VALUES(NULL);

1 row created.

SQL> ALTER TABLE A ADD PRIMARY KEY(A) NOVALIDATE;

Table altered.

SQL>
SQL> SELECT
2 C.Constraint_Name,
3 C.Validated,
4 C.Status,
5 I.Uniqueness
6 FROM
7 User_Constraints C,
8 User_Indexes I
9 WHERE
10 C.Table_Name = ‘A’
11 AND I.Index_Name = C.Constraint_Name;

CONSTRAINT_NAME VALIDATED STATUS UNIQUENES
—————————— ————- ——– ———
SYS_C0029599 NOT VALIDATED ENABLED UNIQUE

However, if this was just a UNIQUE CONSTRAINT, that would seem like everything it wanted to do was already done:

SQL> CREATE TABLE A AS SELECT ‘A’ A FROM Dual UNION ALL SELECT

Table created.

SQL> ALTER TABLE A ADD UNIQUE(A) NOVALIDATE;
ALTER TABLE A ADD UNIQUE(A) NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (CHACHAM.SYS_C0029602) – duplicate k

SQL> DELETE FROM A WHERE RowNum = 1;

1 row deleted.

SQL> ALTER TABLE A ADD UNIQUE(A) NOVALIDATE;

Table altered.

SQL>
SQL> SELECT
2 C.Constraint_Name,
3 C.Validated,
4 C.Status,
5 I.Uniqueness
6 FROM
7 User_Constraints C,
8 User_Indexes I
9 WHERE
10 C.Table_Name = ‘A’
11 AND I.Index_Name = C.Constraint_Name;

CONSTRAINT_NAME VALIDATED STATUS UNIQUENES
—————————— ————- ——– ———
SYS_C0029603 NOT VALIDATED ENABLED UNIQUE

4. Richard Foote - July 28, 2008

Hi Brian

Yes, very very good point !!

More on all this to come …

ankur jain - April 5, 2010

yaar unique index can have null values dats y ur primary key constraint gets enabled with novalidate clause only .

Richard Foote - April 13, 2010

Hi Ankur

?????

5. Asif Momen - July 28, 2008

Hi Brian,

Good one !!!

6. B. Polarski - July 30, 2008

Great reading, really. You successfully made crystal clear out of confusion.

7. Richard Foote - July 30, 2008

Thanks for the nice comments B. Polarski, as always much appreciated :)

ankur jain - April 17, 2010

richard u great

8. Roni - June 9, 2010

Thank you very much for a useful post.

I was reading about NOVALIDATE Constraints and I just though about the issue with unique indexes and suddenly found your blog.
You sort this issue for me.

Have a great day,
Roni.

Richard Foote - July 20, 2010

Hi Roni

Glad you found the post useful :)

9. Senti - August 2, 2010

Excellent folks… I got a clear isea abt NOVALIDATE. :-)

Richard Foote - August 10, 2010

Hi Senti

Cool, nice to know :)

10. Nandhakumar - December 2, 2010

Hi Richard, your article and Brian’s commnets have made the NOVALIDATE and VALIDATE options related to constraints very clear. Thanks for your article

11. Jai - June 3, 2011

Hi,

Normally, While adding Unique or Primary key constraint, Oracle create Unique index.
but in this case it is creating non-unique.

You have any idea?

Take Care

T

Richard Foote - June 6, 2011

Hi Jai

Not exactly clear to me what you mean by “in this case” ?

Is there something in the article you don’t understand or do you have a specific example in mind ?

12. Pratik Mehta - October 13, 2011

Thanks to Richard Foote, Oracle has lots of such miniscule problems. This is one of them…One i came across in last couple of weeks was UNLIMITED PRIVILEGE available to the user who is granted RESOURCE role, as dba_sys_privs does not mention this privilege when GRANTEE=RESOURCE… and worse part is that its difficult to find anywhere on Oracle’s official documentation

13. bharatvekariya22 - February 1, 2014

I am using oracle forms. i applied ckeck constraint not null with novalidate. but when ever new data gets updated it checks the old data and through me not null error. Is there any solution for this?
Kindly Guide.

Richard Foote - February 14, 2014

Tricky :)

If you have the novalidated constraint in place but update a row with nulls currently in the column, then the update will fail unless the not null column is populated at this time.

No magical solution I’m afraid, best option/compromise depends on the importance of the associated business rules.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,715 other followers

%d bloggers like this: