/* Oracle 11g */
I need to create a constraint that checks that multiple columns are unique.
But that constraint is enabled just if a 3rd column contains a certain value.
The table already has non unique rows, so the constraint bust be no validated.
In the following example
CREATE TABLE EMPS (ID NUMBER, EMP VARCHAR2(10), DEPT VARCHAR2(10), STATUS NUMBER);
INSERT INTO EMPS VALUES (1, 'JOHN', 'MARKETING', 1);
INSERT INTO EMPS VALUES (2, 'JANE', 'MARKETING', 1);
UPDATE EMPS SET STATUS = 0 WHERE ID = 1;
INSERT INTO EMPS VALUES (3, 'JOHN', 'SALES', 1);
INSERT INTO EMPS VALUES (4, 'JOHN', 'SALES', 1); ---- ups, it's a error... need some constraint so I won't do it again
SELECT * FROM EMPS;
"ID" "EMP" "DEPT" "STATUS"
1 "JOHN" "MARKETING" 0
2 "JANE" "MARKETING" 1
3 "JOHN" "SALES" 1
What I want is:
INSERT INTO EMPS VALUES (5, 'JOHN', 'MARKETING', 1);
1 row inserted
INSERT INTO EMPS VALUES (5, 'JOHN', 'SALES', 1);
Constraint violated
That is, I need (EMP, DEPT) to be unique IF STATUS = 1
Is it doable?
Thanks for any help