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