Re: Composite Key Using Year from a Date

From: jimmyb <jimmybrock_at_gmail.com>
Date: Fri, 22 Apr 2011 10:21:35 -0700 (PDT)
Message-ID: <f95f44de-f653-4a26-abff-e39a91fdeecf_at_r33g2000prh.googlegroups.com>

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 Received on Fri Apr 22 2011 - 12:21:35 CDT