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?
Advertisement
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>