SYSDATE in a CHECK constraint

From: mark m <mockm_at_my-deja.com>
Date: Tue, 14 Dec 1999 15:33:56 GMT
Message-ID: <835o14$6tq$1_at_nnrp1.deja.com>

 This...

alter table fac_contracts
[Quoted]  add(constraint fcon_uq

     unique (fac_id, company_id, team_id, department_id, contract_type_id, start_date)

     using index pctfree 10
      tablespace tw_indexes
      storage (initial 160K
               next 160K))

 add(constraint fcon_current_ck
     check (start_date < sysdate and (end_date > sysdate or end_date is

null)));

...produces this...

SQLWKS> alter table fac_contracts
     2>  add(constraint fcon_uq
     3>      unique (fac_id, company_id, team_id, department_id,
contract_type_id, start_date)
     4>      using index pctfree 10
     5>       tablespace tw_indexes
     6>       storage (initial 160K
     7>                next 160K))
     8>
     9>  add(constraint fcon_current_ck
    10>      check (start_date < sysdate and (end_date > sysdate or
end_date is null)));
     check (start_date < sysdate and (end_date > sysdate or end_date is
null)))
                         *

ORA-02436: date or system variable wrongly specified in CHECK constraint

...which is explained as this...

ORA-02436: date or system variable wrongly specified in CHECK constraint
Cause: An attempt was made to use a date constant or system variable, such as USER, in a check constraint that was not completely specified in a CREATE TABLE or ALTER TABLE statement. For example, a date was specified without the century.
Action: Completely specify the date constant or system variable.

--

Does anyone know what is going on here with the SYSDATE in the CHECK constraint? Is there something that needs to be done for this to work?

Mark

--

 mark m _at_ team health

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 16:33:56 CET