NOVALIDATE constraints – Primary Key violated. Duplications…

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.

There are a few little traps one can easily fall into if these two very important fundamentals are not understood:

  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”

If we try 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.

It’s easy to introduce duplicate rows of data into Oracle tables by running a data load twice without the primary key or unique indexes created or enabled.
Here’s how you remove the duplicate rows before the primary key or unique indexes can be created:

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3 ;

Here column1, column2, column3 constitute the identifying key for each record.
The GROUP BY is used on the columns that make the primary key for the table. This script deletes each row in the group after the first row.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: