Re: Composite Key Using Year from a Date

From: S. Anthony Sequeira <nobody_at_127.0.0.1>
Date: Sat, 23 Apr 2011 00:22:49 +0100
Message-ID: <4db20dca$0$2530$da0feed9_at_news.zen.co.uk>



On 22/04/11 23:16, jimmyb wrote:
> 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 from
> update_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(year
> from 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.
>

You can make it a date datatype if you use trunc. This may work with extract, try it.

SQL> drop table user_competencies purge; 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 (trunc(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
       );

select * from user_competencies;

Table dropped.

SQL> 2 3 4 5 6 7
Table created.

SQL> 2
Table altered.

SQL> 2 3 4 5 6 7 8
1 row created.

SQL>
    USER_ID COMP_ID U S UPDATE_DT UPDATE_YR ---------- ---------- - - --------- ---------

        10 1 a a 22-APR-11 01-JAN-11

SQL>

-- 
Tony Sequeira
++
Received on Fri Apr 22 2011 - 18:22:49 CDT

Original text of this message