Enabling Constraints even if existing data violates the constraints   March 6th, 2008

in other words we want to Enabling constraint without checking for validity of historical data

in this example, light is going to shed on how one can enable constraints without ensuring that exiting data stratifies constraint condition.
This is particularly useful in data warehouse application (ETL) and data migration and conversion, where you are supposed to transfer data to a new structure and the old data does not necessarily conform to the new constraints condition

SQL> CREATE TABLE AMMAR (F1 NUMBER);

Table created.

SQL> INSERT INTO AMMAR VALUES (1);


1 row created.

SQL> INSERT INTO AMMAR VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

Apply a primary key constraint

SQL> ALTER TABLE AMMAR ADD CONSTRAINT AMMAR_PK PRIMARY KEY(F1)

Table altered.

The operation is successful, since the data already conforms to the constraint condition
Note: adding a Primary key constraint also create a UNIQUE INDEX on the Column F1 Automatically, the same holds true for UNIQUE Constraints


No try to add a new record that voilates the constraint

SQL> INSERT INTO AMMAR VALUES (2);
INSERT INTO AMMAR VALUES (2)

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.AMMAR_PK) violated

Now try to add a recrod that does not violdate the constraint

SQL> INSERT INTO AMMAR VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

Here i am disabling the constraint

SQL> ALTER TABLE AMMAR DISABLE CONSTRAINT AMMAR_PK;

Table altered.

Note: Disabling the constraint also drops the unique index

SQL> INSERT INTO AMMAR VALUES (3);

1 row created.

The record is accepted because the constrain is disabled (3 already exists)

SQL> COMMIT;

Commit complete.


Now, i will try to enable the constraint using the NOVALIDATE value. The NOVALIDATE value should instruct Oracle not to check for old value already in the table, only the new data shall be checked

SQL> ALTER TABLE AMMAR ENABLE NOVALIDATE CONSTRAINT AMMAR_PK

SQL> ALTER TABLE AMMAR ENABLE NOVALIDATE CONSTRAINT AMMAR_PK

*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.AMMAR_PK) - primary key violated

BUT IT DID NOT WORK, IT IS STILL CHECK !!

The reason is because by default the primary key constraint creates a UNIQUE Index. Here, it is the UNIQUE index that is causing the problem.

To solve this issue, i will manually create an NON-UQNIQUE index, then i will enable the constraint. The Primary Key constraint shall automatically detect the existance of an index on the same colulmn and therefore, shall not attept to recreate another index
SQL> ALTER TABLE AMMAR DISABLE CONSTRAINT AMMAR_PK;

Table altered.

SQL> CREATE INDEX AMMAR_IND ON AMMAR(F1);

Index created.

SQL> ALTER TABLE AMMAR ENABLE NOVALIDATE CONSTRAINT AMMAR_PK;

Table altered.

SQL> SELECT * FROM AMMAR;

F1
----------
1
2
3
3

Note above, that 3 is duplicated and this proves that the enablement of the Primary key constraint did not bother the duplication of row before it was enable

SQL> INSERT INTO AMMAR VALUES (2);
INSERT INTO AMMAR VALUES (2)

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.AMMAR_PK) violated


Here the attempt to insert a violating row is rejected