Re: Composite Key Using Year from a Date
Date: Fri, 22 Apr 2011 15:16:23 -0700 (PDT)
Message-ID: <29dd5d64-57da-45a5-a885-243d2c80cbd9_at_v36g2000prm.googlegroups.com>
On Apr 22, 1:29 pm, ddf <orat..._at_msn.com> wrote:
> 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
Neither select statement to_date, to_char or trunc worked.
Really interesting what it take to make a virtual column work with a date data type.
jbrock_dvlpr_at_ddtms_d> create table user_competencies
2 ( user_id number 3 ,comp_id number 4 ,user_score varchar2(1) 5 ,supervisor_score varchar2(1) 6 ,update_dt date 7 ,update_yr date generated always as(TO_CHAR(update_dt,'yyyy'))
8 )
9 /
Table created.
jbrock_dvlpr_at_ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);
1 row created.
jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
select * from user_competencies
*
ERROR at line 1:
ORA-01861: literal does not match format string
jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge;
Table dropped.
jbrock_dvlpr_at_ddtms_d> create table user_competencies 2 ( user_id number 3 ,comp_id number 4 ,user_score varchar2(1) 5 ,supervisor_score varchar2(1) 6 ,update_dt date 7 ,update_yr date generated always as (TO_DATE(update_dt,'yyyy')) 8 ) 9 /
Table created.
jbrock_dvlpr_at_ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);
1 row created.
jbrock_dvlpr_at_ddtms_d> select * from user_competencies; select * from user_competencies
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input
string
jbrock_dvlpr_at_ddtms_d> drop table user_competencies purge ;
Table dropped.
jbrock_dvlpr_at_ddtms_d> create table user_competencies
2 ( user_id number 3 ,comp_id number 4 ,user_score varchar2(1) 5 ,supervisor_score varchar2(1) 6 ,update_dt date 7 ,update_yr varchar2(4) generated always as (EXTRACT(year fromupdate_dt))
8 )
9 /
,update_yr varchar2(4) generated always as (EXTRACT(year from update_dt))
*
ERROR at line 7:
ORA-12899: value too large for column "UPDATE_YR" (actual: 4, maximum:
40)
Huh??? This I don't get.
jbrock_dvlpr_at_ddtms_d> create table user_competencies
2 ( user_id number 3 ,comp_id number 4 ,user_score varchar2(1) 5 ,supervisor_score varchar2(1) 6 ,update_dt date 7 ,update_yr varchar2(40) generated always as (EXTRACT(yearfrom update_dt))
8 )
9 /
Table created.
jbrock_dvlpr_at_ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);
1 row created.
jbrock_dvlpr_at_ddtms_d> select * from user_competencies;
USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
---------- ---------- - - ---------
----------------------------------------
1 1 22-APR-11 2011
jbrock_dvlpr_at_ddtms_d> alter table user_competencies add constraint user_competencies_pk primary key(user_id,comp_id,update_yr);
Table altered.
Finally, that worked. But I can't make the virtual column a date data type and varchar2 must be defined to hold 40k. Received on Fri Apr 22 2011 - 17:16:23 CDT