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?

On Apr 21, 1:34*pm, jimmyb <jimmybr... (AT) gmail (DOT) com> wrote:
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'))
)
/

On Apr 21, 1:42*pm, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:
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.

On Apr 21, 5:55*pm, jimmyb <jimmybr... (AT) gmail (DOT) com> wrote:

I have also used this VC as my partition key using INTERVAL
partitions. If you are need to use partitioning and are not using I-
P, 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.

On Apr 21, 5:55*pm, jimmyb <jimmybr... (AT) gmail (DOT) com> wrote:
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 --

On Apr 22, 8:12*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
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

On 22/04/11 18:21, jimmyb wrote:
Try

select to_date( sysdate,'yyyy') from dual;

and then try

select trunc(sysdate,'yyyy') from dual;

--
Tony Sequeira
++

On Apr 22, 10:21*am, jimmyb <jimmybr... (AT) gmail (DOT) com> wrote:
Why are you using TO_DATE() against a DATE value? It should be
TO_CHAR, I believe.


David Fitzjarrell

On Apr 22, 1:29*pm, ddf <orat... (AT) msn (DOT) com> wrote:
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@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@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@ddtms_d> select * from user_competencies;
select * from user_competencies
*
ERROR at line 1:
ORA-01861: literal does not match format string

jbrock_dvlpr@ddtms_d> drop table user_competencies purge;

Table dropped.

jbrock_dvlpr@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@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@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@ddtms_d> drop table user_competencies purge ;

Table dropped.

jbrock_dvlpr@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@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@ddtms_d> insert into user_competencies
(user_id,comp_id,update_dt)
2 values(1,1,sysdate);

1 row created.

jbrock_dvlpr@ddtms_d> select * from user_competencies;

USER_ID COMP_ID U S UPDATE_DT UPDATE_YR
---------- ---------- - - ---------
----------------------------------------
1 1 22-APR-11 2011

jbrock_dvlpr@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.

On 22/04/11 23:16, jimmyb wrote:
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
++