Re: SYSDATE in a CHECK constraint

If you really want to check on sysdate, create a database-trigger wich issues an user-defined exception.
mark m <mockm_at_my-deja.com> schreef in berichtnieuws 835o14$6tq$1_at_nnrp1.deja.com...
> This...
>
> alter table fac_contracts
> 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 - 20:53:57 CET