::Novalidate in Oracle, Not Valid in PostgreSQL::

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

Same Case in PostgreSQL 9.1
=============================

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

Step 2:
----------
INSERT INTO PARENT VALUES(GENERATE_SERIES(1,100));

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

INSERT INTO CHILD VALUES(GENERATE_SERIES(200,300));

Step 4:
----------
postgres=# ALTER TABLE CHILD ADD FOREIGN KEY(T) REFERENCES PARENT(T) NOT VALID;
ALTER
postgres=# INSERT INTO CHILD VALUES(1000);
ERROR: insert or update on table "child" violates foreign key constraint "child_t_fkey"
DETAIL: Key (t)=(1000) is not present in table "parent".