Re: Composite Key Using Year from a Date

From: jimmyb <jimmybrock_at_gmail.com>
Date: Thu, 21 Apr 2011 14:55:07 -0700 (PDT)
Message-ID: <c14de8fb-9eb6-4ce6-b1cc-a0abd148073e_at_a11g2000pro.googlegroups.com>

On Apr 21, 1:42 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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'))
> )
> /

Thanks, onedbguru! Yes, I am using 11g R2...I should have included that in my OP.

I forgot about the virtual column feature of 11g R2. That should work. Received on Thu Apr 21 2011 - 16:55:07 CDT