Term: VALIDATE

Definition:
In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column in a table. A constraint can be explicitly added to a table column in ENABLED or DISABLED mode with VALIDATE or NOVALIDATE states. A description of the states is outlined below.

ENABLE VALIDATE - If the old and new data comply with the constraint logic, the constraint in enabled, else it is in DISABLED mode. This is the default state for the ENABLED mode.
ENABLE NOVALIDATE - The constraint would be enabled without validating the constraint logic for the old existing data. Only the fresh new data would comply with the constraint logic.
DISABLE VALIDATE - Oracle server creates a valid constraint in DISABLED mode, with no index information. TNote that in this case the constrained columns in the table cannot be modified later.
DISABLE NOVALIDATE - The constraint is created in DISABLED mode without validation of fresh and older data. It is the default state for DISABLED mode.

Example Syntax:

ALTER TABLE [TABLE NAME] 

ADD CONSTRAINT [CONSTRAINT SPECIFICATION]  

[ENABLE VALIDATE | ENABLE NOVALIDATE | 

DISABLE VALIDATE | DISABLE NOVALIDATE]

Example Usage:

The SQL below adds a referential integrity constraint on the DEPTID column of EMP table in NOVALIDATE state. Note that earlier transactions on the column were successful. Since the constraint was enabled in NOVALIDATE state, it skips the validation of old data and creates the constraint. A new transaction violates the constraint and raises ORA-02291 exception.

SQL> CREATE TABLE DEPT

 (DEPTNO NUMBER,

  DEPTNAME VARCHAR2(100));



Table created.



SQL> CREATE TABLE EMP

 (EMPNO NUMBER PRIMARY KEY,

  ENAME VARCHAR2(100),

  DEPTID NUMBER);



Table created.



SQL> INSERT INTO DEPT VALUES (1, 'ADM');

1 row created.



SQL> INSERT INTO DEPT VALUES (2, 'DEV');

1 row created.



SQL> INSERT INTO DEPT VALUES (3, 'HR');

1 row created.

  

SQL> INSERT INTO EMP VALUES  (100, 'ABC', 10);

1 row created.



SQL> INSERT INTO EMP VALUES  (101, 'XYZ', 20);

1 row created.



SQL> ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO  FOREIGN KEY ( DEPTID )  REFERENCES DEPT( DEPTNO)  ENABLE  NOVALIDATE;



Table altered.



SQL> INSERT INTO EMP VALUES  (102, 'KYT', 20);



INSERT INTO EMP

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found