Re: Composite Key Using Year from a Date

From: onedbguru <onedbguru_at_yahoo.com>
Date: Thu, 21 Apr 2011 17:43:39 -0700 (PDT)
Message-ID: <845033cb-b67f-43a3-89a8-24a8573480ac_at_o21g2000prh.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.

I have also used this VC as my partition key using INTERVAL partitions. If you are need to use partitioning and are not using IP,  you are causing yourself a whole lot of work by having to ensure that someone - or some process ran the "add partition" scripts. I have seen interval partitions at a ridiculously fine interval. Received on Thu Apr 21 2011 - 19:43:39 CDT

Original text of this message