Ora-2290 On Insert Into Table With Temporal Validity

Last updated on DECEMBER 06, 2016

Applies to:

Symptoms

Insert into table with temporal validity raises ORA-2290

SQL> insert into presidents1 values (
  2      'John F. Kennedy'   , date '1917-05-29', date '1963-11-22'
  3  )
  4  /
insert into presidents1 values (
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.ALIVEEB452E) violated


Hidden virtual column ALIVE has default a negative integer.

SQL> select tc.column_name
  2        , tc.data_type
  3        , tc.column_id
  4        , tc.data_default
  5        , tc.hidden_column
  6        , tc.virtual_column
  7    from user_tab_cols tc
  8   where tc.table_name = 'PRESIDENTS1'
  9   order by tc.column_id
 10  /

COLUMN_NAME          DATA_TYPE   COLUMN_ID DATA_DEFAULT         HID VIR         
-------------------- ---------- ---------- -------------------- --- ---         
NAME                 VARCHAR2            1                      NO  NO          
BIRTH                DATE                2                      NO  NO          
DEATH                DATE                3                      NO  NO          
ALIVE                NUMBER                -1645525714          YES YES         

 
Constraint is auto-created requiring ALIVE to be greater than zero?

SQL>
SQL> select uc.constraint_name
  2        , uc.constraint_type
  3        , uc.search_condition
  4    from user_constraints uc
  5   where uc.table_name = 'PRESIDENTS1'
  6  /

CONSTRAINT_NAME      C SEARCH_CONDITION                                         
-------------------- - ---------------------------------------------            
ALIVEEB452E          C (BIRTH < DEATH) and (ALIVE > 0)                          

Cause