Re: Simple Check Constraint Condition Question...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Sep 1999 07:43:36 -0400
Message-ID: <Y+7YNwZuoRExINAt3qpHX0LglnkJ@4ax.com>

A copy of this was sent to Stephen Hurrell <hurrells_at_baynet.net> (if that email address didn't require changing) On Fri, 10 Sep 1999 01:59:12 -0400, you wrote:

>Hello.
>
>I have a simple table example;
>
>create table events (
> event_year varchar2(4) not NULL,
> event_date date not NULL
>)
>
>What I want is to constrain event_year to be equal to the
>year portion of event_date. Event_year is entered as a YYYY value.
>
>I think that this is close to what I want in the constraint.
>
>event_year = to_date(event_date, 'YYYY')
>
>However I get a ORA-2436: date or system variable wrongly specified
>in CHECK constraint.
>
>Does this mean that I should be translating the date to a string?

yes -- you cannot to_date a DATE -- it is already a date.

it would look like this:

  1 create table events (

  2                  event_year    varchar2(4) not NULL,
  3                  event_date    date not NULL,
  4                             check (event_year = to_char(event_date,'YYYY'))

  5* )
tkyte_at_8.0> /

Table created.

but you could also just put a trigger on:

create or replace trigger T
before insert or update on events
for each row
begin
  :new.event_year := to_char(:new.event_date,'YYYY'); end;

that way -- regardless of what the client tried to put in there, it would have the data you want in there. The client would not have to even populate this field -- the trigger would.

>
>STeve
>-----EOT
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 10 1999 - 06:43:36 CDT