Composite Key Using Year from a Date

From: jimmyb <jimmybrock_at_gmail.com>
Date: Thu, 21 Apr 2011 10:34:40 -0700 (PDT)
Message-ID: <6366cb82-ffd4-42ac-9a04-6ef8ae0a3f18_at_z7g2000prh.googlegroups.com>

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? Received on Thu Apr 21 2011 - 12:34:40 CDT