Unique constraint on only ACTIVE Value

You might have come across a situation where you need to constraint the values in a flag column in such a way that it could contain multiple records with Non-Active values (“N”) but there should be only one record for Active value (“Y”).

How do you handle that?

Validate in your code itself before INSERT/UPDATE whether there is already an active record or not, and if it is there raise an exception to fail the current INSERT/UPDATE.

Let is check it here by an example how can be achieve this in a better way.

DROP TABLE DEPT;

CREATE TABLE DEPT

(

   DEPTID                  NUMBER

  ,DEPTNAME                VARCHAR2(20)

  ,DEPT_MANAGER_ID         NUMBER

  ,ACTIVE_MANAGER          VARCHAR2(1)

  ,ACTIVE_START_DATE       DATE

  ,ACTIVE_END_DATE         DATE

);

INSERT INTO DEPT VALUES (100,'HR',1,'Y', TRUNC(SYSDATE), NULL);

INSERT INTO DEPT VALUES (100,'HR',2,'N', TRUNC(SYSDATE) -100, TRUNC(SYSDATE)-1);

INSERT INTO DEPT VALUES (100,'HR',3,'N', TRUNC(SYSDATE) -200, TRUNC(SYSDATE)-99);

INSERT INTO DEPT VALUES (200,'FINANCE',4,'Y', TRUNC(SYSDATE), NULL);

INSERT INTO DEPT VALUES (200,'FINANCE',5,'N', TRUNC(SYSDATE) -100, TRUNC(SYSDATE)-1);

INSERT INTO DEPT VALUES (200,'FINANCE',6,'N', TRUNC(SYSDATE) -200, TRUNC(SYSDATE)-99);

INSERT INTO DEPT VALUES (300,'IT',7,'Y', TRUNC(SYSDATE), NULL);

COMMIT;

SELECT * FROM DEPT;

SELECT * FROM DEPT WHERE ACTIVE_MANAGER = 'Y' ORDER BY DEPTID;

Now the requirement is that below insert or update to DEPT table should fail as there can’t be two active managers for a department at a given time.

Without any constraint/Validation Logic the below queries will succeed without any failure.

INSERT INTO DEPT VALUES (100,'HR',9,'Y', TRUNC(SYSDATE), NULL);

UPDATE DEPT

   SET ACTIVE_MANAGER   = 'Y'

      ,ACTIVE_END_DATE  = NULL

 WHERE DEPTID           = 200

   AND DEPT_MANAGER_ID  = 5

   AND ACTIVE_MANAGER   = 'N';

SELECT * FROM DEPT WHERE ACTIVE_MANAGER = 'Y' ORDER BY DEPTID;

ROLLBACK;

How would you handle this?

Probably query the DEPT table before INSERT/UPDATE and check whether there is an active manager for that particular department

SELECT COUNT(1)

  FROM dept

WHERE ACTIVE_MANAGER = 'Y'

   AND DEPTID         = &DEPTID;

IF COUNT is > 0 raise an exception to stop INSERT/UPDATE making two active managers for a particular department.

That is the conventional way we generally use.

There is a better way where Oracle can handle it by itself without us writing the validation query.

A Unique functional Index on DEPTID and ACTIVE_MANAGER('Y') using the CASE statement.

CREATE UNIQUE INDEX UN_DEPT ON DEPT( CASE WHEN ACTIVE_MANAGER = 'Y' THEN DEPTID END);

INSERT INTO DEPT VALUES (100,'HR',9,'Y', TRUNC(SYSDATE), NULL);

UPDATE DEPT

   SET ACTIVE_MANAGER   = 'Y'

      ,ACTIVE_END_DATE  = NULL

 WHERE DEPTID           = 200

   AND DEPT_MANAGER_ID  = 5

   AND ACTIVE_MANAGER   = 'N';

Both the statements will raise an error, ORA-00001: unique constraint (PPID_DATA.UN_DEPT) violated.

In case the requirement is combination of 2 columns and ACTIVE_MANAGER create index as below.

CREATE UNIQUE INDEX UN_DEPT ON DEPT( CASE WHEN ACTIVE_MANAGER = 'Y' THEN COL1 END, CASE WHEN ACTIVE_MANAGER = 'Y' THEN COL2 END);

And if it is only ACTIVE_MANAGER and no other column then (A different situation where there can be only one record with ACTIVE_FLAG = ‘Y’ but many with value as NULL or ‘N’)

CREATE UNIQUE INDEX UN_DEPT ON DEPT( CASE WHEN ACTIVE_MANAGER = 'Y' THEN ACTIVE_MANAGER END);