Constraints: ENABLE NOVALIDATE

Yesterday while perusing the Concepts Guide, I stumbled across the ENABLE NOVALIDATE keywords for the definition of a Foreign Key constraint. I've always known it was there, just never used it, or thought to use it.

It can be a big benefit while working on a legacy system.

Suppose you have a table, T_CHILD:

CREATE TABLE t_child
(
  child_id NUMBER(10)
    CONSTRAINT pk_childid PRIMARY KEY,
  soon_to_be_parent_id NUMBER(10)
);

INSERT INTO t_child
  ( child_id,
    soon_to_be_parent_id )
SELECT
    rownum,
    TRUNC( dbms_random.value( -9999, -1 ) )
FROM dual
  CONNECT BY LEVEL <= 10;

This table has been around for quite some time. You decide that you would like to constrain the values in the SOON_TO_BE_PARENT_ID column. First, here's the data that exists:

CJUSTICE@TESTING>SELECT * FROM t_child;

  CHILD_ID SOON_TO_BE_PARENT_ID
---------- --------------------
         1                -5560
         2                -1822
         3                -2499
         4                -7039
         5                -8718
         6                -1019
         7                -9997
         8                -9553
         9                -4477
        10                -1458

Now I'll create a table that will contain the values I want to constraint SOON_TO_BE_PARENT_ID to, call it a lookup or reference table.

CREATE TABLE t_parent
(
  parent_id NUMBER(10)
    CONSTRAINT pk_parentid PRIMARY KEY
);

I'll populate it with some data:

INSERT INTO t_parent( parent_id )
SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 10;

CJUSTICE@TESTING>SELECT * FROM T_PARENT;

 PARENT_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Now I'll add the constraint that references the PARENT_ID column of T_PARENT

ALTER TABLE t_child
  ADD CONSTRAINT fk_parentid
  FOREIGN KEY ( soon_to_be_parent_id )
  REFERENCES t_parent( parent_id )
  ENABLE
  NOVALIDATE;

and rename the column to PARENT_ID:

ALTER TABLE t_child RENAME COLUMN soon_to_be_parent_id TO parent_id;

What will this do? I should no longer be able to enter a value into T_CHILD.PARENT_ID that does not exist in T_PARENT, but it will ignore anything that already exists.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    11 );

INSERT INTO t_child
*
ERROR at line 1:
ORA-02291: integrity constraint (CJUSTICE.FK_PARENTID) violated - parent key not found

Perfect! Now I'll add a value that does exist in T_PARENT.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    10 );

1 row created.

Win!

This is just another reminder why you must read the Concepts Guide. By the way, I found the quote I was looking for from Mr. Kyte (h/t @boneist)

"...if you simply read the Concepts Guide...and retain just 10%..., you’ll already know 90% more than most people do"