Re: Composite Key Using Year from a Date

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 22 Apr 2011 08:12:46 -0700 (PDT)
Message-ID: <162ad5a7-61a8-4dba-bdcb-7a80f7b94155_at_22g2000prx.googlegroups.com>

On Apr 21, 5:55 pm, jimmyb <jimmybr..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Interesting solution but I have a couple of thoughts. The solution may not work if update_dt is the date the record (row) was last updated rather than the certification period depending when the data is updated. Depending on what kind of certifications are being tracked it is possible the certification period may cross calendar years in which case the table as displayed may need some additional columns. Neither of these conditions may apply but I thought I would mention the potential issues.

HTH -- Mark D Powell -- Received on Fri Apr 22 2011 - 10:12:46 CDT