Re: Composite Key Using Year from a Date

From: ddf <oratune_at_msn.com>
Date: Fri, 22 Apr 2011 13:29:12 -0700 (PDT)
Message-ID: <2b058e3c-7703-4751-8d13-ccb944dc45ca_at_q30g2000vbs.googlegroups.com>

On Apr 22, 10:21 am, jimmyb <jimmybr..._at_gmail.com> wrote:
> On Apr 22, 8:12 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
>
>
> > 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 --
>
> Thanks for your input Mark.
>
> Users canupdate the record as often as they need - within the calendar
> year. Once a calendar year changes they should not be making any
> updates. The application has a business rule only to display
> competencies for the current year.
>
> I have another problem with this solution though. I'm an error when I
> insert a record.
>
> 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'))
> )
> /
>
> alter table user_competencies add constraint user_comp_pk primary
> key(user_id,comp_id,update_yr);
>
> insert into user_competencies
> (user_id, comp_id, user_score, supervisor_score, update_dt)
> values(   10
>         , 1
>         , 'a'
>         , 'a'
>         , sysdate-1
>       ) ;
>
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input
> string- Hide quoted text -
>
> - Show quoted text -

Why are you using TO_DATE() against a DATE value? It should be TO_CHAR, I believe.

David Fitzjarrell Received on Fri Apr 22 2011 - 15:29:12 CDT