Is it unique?

The other day one of my colleagues said something like: ‘Hey, this is strange. Oracle is not working correctly’. My first thought is then: ‘well, you probably didn’t specify you requirements correctly’. What was the problem? A unique index was created on a table to implement a business rule which can be rephrased into ‘One-Manager-Per-Department’. This includes multiple columns where any combination is allowed, but only one manager is allowed per department.

First, let’s make a simplified version of the well known EMP table.

CREATE TABLE emp
( empno  NUMBER(4)
, ename  VARCHAR2(10)
, job    VARCHAR2(9)
, deptno NUMBER(2)
)
/

Then we try creating a unique index on the job column:

CREATE UNIQUE INDEX ui_one_manager ON emp (job)
/

Insert some data:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);

And notice that you cannot enter two employees with the same job, which is correct since we said the JOB should be unique. But we just want the MANAGER to be unique. I remembered that NULL values will not be included in the index so let’s try this:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END)
/

Again insert some data:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7566, 'JONES',  'MANAGER',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7698, 'BLAKE',  'MANAGER',   30);

Doing a lot better, but we cannot enter more than one MANAGER, even if they are in different departments. Let’s include the deptno in the columns for the index:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (deptno
                                          , CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END
                                          )
/

And try some data again:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);

Pretty much back where we started.

How about we apply the same case statement to the DEPTNO column like this:

DROP INDEX ui_one_manager
/
CREATE UNIQUE INDEX ui_one_manager ON emp (CASE job
                                             WHEN 'MANAGER' THEN deptno
                                             ELSE NULL
                                           END
                                          , CASE job
                                             WHEN 'MANAGER' THEN job
                                             ELSE NULL
                                           END
                                          )
/

And try some data again:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7369, 'SMITH',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7499, 'ALLEN',  'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7521, 'WARD',   'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7566, 'JONES',  'MANAGER',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7698, 'BLAKE',  'MANAGER',   30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7782, 'CLARK',  'MANAGER',   10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7788, 'SCOTT',  'ANALYST',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7839, 'KING',   'PRESIDENT', 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN',  30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7876, 'ADAMS',  'CLERK',     20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7900, 'JAMES',  'CLERK',     30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7902, 'FORD',   'ANALYST',   20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (7934, 'MILLER', 'CLERK',     10);
And this time it worked.

To check if the unique index really works let’s try some more inserts:

INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, DEPTNO) VALUES (2912, 'BAREL', 'MANAGER', 40);

Only the last one works. Which is what was expected.

One thought on “Is it unique?


Leave a Reply

Your email address will not be published. Required fields are marked *