Novalidate in Oracle

Child entry in Oracle without Refference
=========================================

Yes, this is possible in Oracle as well PostgreSQL.

Adv:-
------
1. Doesn't Lock the table.So, no outage.
2. It doesn't validate the child entries with parent.So, no performance issues.
3. This option is very useful, when you are handling very big tables.

Step 1:
----------
CREATE TABLE PARENT(T INT PRIMARY KEY);

Step 2:
----------
Insert sample data

SQL> SELECT * FROM PARENT;

T
----------
1
2
3

Step 3:
----------
CREATE TABLE CHILD(T INT);

SQL> SELECT * FROM CHILD;

T
----------
1
2
3
4
5

Step 4:
----------
SQL> ALTER TABLE CHILD ADD FOREIGN KEY(T) REFERENCES PARENT(T) ENABLE NOVALIDATE;

Step 5:
----------
SQL> SELECT * FROM CHILD;

T
----------
1
2
3
4
5

SQL> INSERT INTO CHILD VALUES(10);
INSERT INTO CHILD VALUES(10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.SYS_C006394) violated - parent key not
found