Constraint stats

Table constraint can be enabled or disabled by using CREATE TABLE or ALTER TABLE statement. In addition, the VALIDATE or NOVALIDATE keywords can be used to alter the action of the state. This is introduced in oracle8i.

Here are the four type of constraint stats. These four constraint stats are applicable for all type of constraints(primary key, foreign key, check etc).

1. ENABLE VALIDATE
2. ENABLE NOVALIDATE
3. DISABLE VALIDATE
4. DISABLE NOVALIDATE

ENABLE VALIDATE is same as ENABLE. Constraint validate the data as soon as we entered in the table.

scott@ordb> CREATE TABLE MASTER(id NUMBER(10));

Table created.

scott@ordb> CREATE TABLE child(id NUMBER(10), master_id NUMBER(10));

Table created.

scott@ordb> ALTER TABLE master ADD PRIMARY KEY (id);

Table altered.

scott@ordb> ALTER TABLE child ADD CONSTRAINT fk_master
2 FOREIGN KEY (master_id)
3 REFERENCES master
4 ENABLE VALIDATE;

Table altered.

scott@ordb> INSERT INTO master VALUES(1);

1 row created.

scott@ordb> INSERT INTO child VALUES(12,12);
INSERT INTO child VALUES(12,12)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@ordb> COMMIT;

Commit complete.

scott@ordb>

ENABLE NOVALIDATE is not same as ENABLE. Constraint validates the new data or modified data. It would not validate the existing data in table.

scott@ordb> insert into master values(1);

1 row created.

scott@ordb> alter table child disable constraint fk_master;

Table altered.

scott@ordb> INSERT INTO child VALUES(12,12);

1 row created.

scott@ordb> commit;

Commit complete.

scott@ordb> alter table child enable constraint fk_master;
alter table child enable constraint fk_master
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_MASTER) - parent keys not found

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER ENABLE NOVALIDATE;

Table altered.

scott@ordb> INSERT INTO child VALUES(12,12);
INSERT INTO child VALUES(12,12)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_MASTER) violated - parent key not
found

scott@ordb> INSERT INTO child VALUES(1,1);

1 row created.

scott@ordb> commit;

Commit complete.

scott@ordb> select * from master;

ID
----------
1

scott@ordb> select * from child;

ID MASTER_ID
---------- ----------
1 1
12 12

scott@ordb>

DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER DISABLE NOVALIDATE;

Table altered.

scott@ordb> INSERT INTO CHILD VALUES(88,88);

1 row created.

scott@ordb> COMMIT;

Commit complete.

scott@ordb> select * from master;

ID
----------
1

scott@ordb> select * from child;

ID MASTER_ID
---------- ----------
12 12
1 1
88 88

scott@ordb>

DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.

scott@ordb> ALTER TABLE CHILD MODIFY CONSTRAINTS FK_MASTER DISABLE VALIDATE;

Table altered.

scott@ordb> insert into master values(1);

1 row created.

scott@ordb> insert into child values(1,1);
insert into child values(1,1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.FK_MASTER)
disabled and validated

scott@ordb>

Note : Couple of things needs to be noted down here.

1. Converting NOVALIDATE constraint to VALIDATE would take longer time, depends on how big the data in the table. Although conversion in the other direction is not an issue

2. Disabling primary key constraint will drop the index associated with primary key. Again, when we enable the primary key constraint, it will create the index on the primary key column.

What is the ideal place to use ENABLE NOVALIDATE option?

In a busy environment, some one disabled the constraint accidently or intentionally, and we have already bad data in that table. Now business requested you to load the new set of data, but business wanted to make sure that new set of data should be validated during the load. At this circumstances, we can use ENABLE NOVALIDATE option. This option will validate the new data and old data will not be validated.

What is the ideal place to use DISABLE VALIDATE option?

We disabled the constraint for some reason. We do not want to load any data until we fix the issue and enable the constraint. We can use DISABLE VALIDATE option here. This option would not let you load any data when the constraint is disabled.