Re: Composite Key Using Year from a Date

From: onedbguru <onedbguru_at_yahoo.com>
Date: Thu, 21 Apr 2011 13:42:18 -0700 (PDT)
Message-ID: <5aab60bc-f7e8-45ed-9ed1-ec8111098e62_at_d27g2000vbz.googlegroups.com>

On Apr 21, 1:34 pm, jimmyb <jimmybr..._at_gmail.com> wrote:
> I need to create a composite primary/unique constraint on a table
> based on a year.
>
> It is a competency rating table where each user should only have one
> record per competency per year.
>
> create table user_competencies
> (   user_id   number
>    ,comp_id   number
>    ,user_score varchar2(1)
>    ,supervisor_score varchar2(1)
>    ,update_dt date
> )
> /
>
> alter table user_competencies add constraint user_competencies_pk
> primary key(user_id, comp_id, extract(year from update_dt));
>
> alter table user_competencies add constraint user_competencies_pk
> primary key(user_id, comp_id, extract(year from update_dt))
>
> *
> ERROR at line 1:
> ORA-00904: : invalid identifier
>
> Evidently, I cannot use the Extract function in a constraint.  It
> there anyway to do this without changing the table design?

If you are using 11gR2 you can use "generated always as" and then use that column in your constraint.

create table user_competencies

 (   user_id   number
    ,comp_id   number
    ,user_score varchar2(1)
    ,supervisor_score varchar2(1)
    ,update_dt date
     ,update_yr date generated always as (TO_DATE(update_dt,'YYYY'))

)
/ Received on Thu Apr 21 2011 - 15:42:18 CDT