create a composite unique key constraint with only the year part of date

I have a composite unique constraint that I need to implement to my table, it contains these columns: N, year (part of DATE_), ID_OPERATION.

The code I tried to write was not correct

ALTER TABLE my_table
ADD CONSTRAINT UNIQUE_EXTRAIT UNIQUE (N, EXTRACT(year from DATE_), ID_OPERATION)

is it possible and how?

Answer

Constraint won’t work (as far as I can tell), but unique index will.

SQL> create table test as select ename, hiredate from emp where deptno = 10;

Table created.

SQL> select * from test order by ename;

ENAME      HIREDATE
---------- ----------
CLARK      09.06.1981
KING       17.11.1981
MILLER     23.01.1982

This won’t work:

SQL> alter table test add constraint uk_test
  2  unique(ename, extract(year from hiredate));
unique(ename, extract(year from hiredate))
              *
ERROR at line 2:
ORA-00904: : invalid identifier

But this will:

SQL> create unique index ui1_test on test (ename, extract (year from hiredate));

Index created.

Testing:

SQL> insert into test values ('CLARK', date '1981-01-01');
insert into test values ('CLARK', date '1981-01-01')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UI1_TEST) violated


SQL> insert into test values ('CLARK', date '1985-01-01');

1 row created.

SQL>