Re: What's wrong with this picture?

AFAIK you can't use functions in check constraints. As you probably already found out, you also can't sysdate directly. You need to address this by writing a trigger.

Hth,

--
Sybrand Bakker, Oracle DBA
mark m <mockm_at_my-deja.com> wrote in message news:84b9h2$hcu$1_at_nnrp1.deja.com...
> I have a function...
>
> create or replace function current_date
> return date as
>
> the_date date;
>
> begin
>
> select sysdate
> into the_date
> from sys.dual;
>
> return (the_date);
>
> end;
>
> ...which when used in this constraint...
>
> alter table fac_contracts
> add(constraint fcon_current_ck
> check (start_date < current_date and (end_date > current_date or
> end_date is null)));
>
> ...produces this error...
>
> SQLWKS> alter table fac_contracts
> 2> add(constraint fcon_current_ck
> 3> check (start_date < current_date and (end_date >
> current_date or end_date is null)));
> check (start_date < current_date and (end_date > current_date or
> end_date is null)))
> *
> ORA-00904: invalid column name
>
> What am I missing?
>
>
> --
>
> mark m @ team health
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Dec 28 1999 - 16:26:18 CST