Thanks for the question, MADHU.

Asked: March 30, 2023 - 4:29 pm UTC

Last updated: April 14, 2023 - 12:58 pm UTC

Version: 19

Viewed 1000+ times

You Asked

Hi All,

I am trying to build a conditional unique key constraint where based on specific data availability, I need to allow duplicates or no duplicates.

Ex:
I get to insert data with deptno=30 and deptname =SALES; then I can have EMP=123 with more than one record whose DEPTNO=30.

emp table
EMPNO DEPTNO DNAME SAL
123 30 SALES 1000
123 30 SALES 1000
123 30 SALES 1000

But should not allow duplicate for any employee record whose DEPTNO'S deptname is not SALES.
EMPNO DEPTNO DNAME SAL
123 40 ACCOUNTING 1000 (No more than one record for a given EMPNO and DEPTNO should be present as the deptname <> SALES)

I created the below code, and it is failing due to a Mutating error. Appreciate your help here.



create table dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);

-- 2
 create table emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint pk_emp primary key (empno),   
  constraint fk_deptno foreign key (deptno) references dept (deptno)   
);

-- 3
 insert into DEPT (DEPTNO, DNAME, LOC) 
values(10, 'ACCOUNTING', 'NEW YORK')
;
-- 4
 insert into dept   
values(20, 'RESEARCH', 'DALLAS')
;
-- 5
insert into dept   
values(30, 'SALES', 'CHICAGO')
;
-- 6
insert into dept  
values(40, 'OPERATIONS', 'BOSTON')
;
-- 7
 insert into emp   
values(   
 7839, 'KING', 'PRESIDENT', null,   
 to_date('17-11-1981','dd-mm-yyyy'),   
 5000, null, 10   
)
;
-- 8
insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
)
;
-- 9
insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
)
;
-- 10
insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
)
;
-- 11
insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
)
;
-- 12
insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
)
;
-- 13
insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
)
;
-- 14
insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
)
;
-- 15
insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
)
;
-- 16
insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
)
;
-- 17
insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
)
;
-- 18
insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
)
;
-- 19
insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
)
;
-- 20
insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
)
;



CREATE OR REPLACE TRIGGER fn_emp_trigger
    FOR INSERT OR UPDATE
    ON emp
COMPOUND TRIGGER
    gv_dname     VARCHAR2 (100);
    gv_emp_rec   emp%ROWTYPE;

    /*
    create global temporary table g_emp on commit preserve rows as select * from emp where 1=2;
    */
    BEFORE STATEMENT
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line ('BEFORE STATMENT');
    END BEFORE STATEMENT;

    BEFORE EACH ROW
    IS
        lv_dname    VARCHAR2 (100);
        ln_check    NUMBER;
        lb_ignore   BOOLEAN := FALSE;

    BEGIN
        SELECT dname
          INTO lv_dname
          FROM dept
         WHERE deptno = :new.deptno;

        gv_dname   := lv_dname;

        IF lv_dname = 'SALES'
        THEN
            lb_ignore   := TRUE;
        END IF;

        IF INSERTING
        THEN
            IF NOT lb_ignore
            THEN
                INSERT INTO g_emp
                    SELECT *
                      FROM emp
                     WHERE     emp.deptno = :new.deptno
                           AND emp.ename = :new.ename; 
            END IF;
        ELSIF UPDATING
        THEN
            IF NOT lb_ignore
            THEN
                NULL;
            END IF;
        END IF;
    END BEFORE EACH ROW;

    AFTER EACH ROW
    IS
        lv_dname    VARCHAR2 (100);
        ln_check    NUMBER;
        lb_ignore   BOOLEAN := FALSE;

    BEGIN
        IF gv_dname = 'SALES'
        THEN
            lb_ignore   := TRUE;
        END IF;

        IF INSERTING
        THEN
            IF NOT lb_ignore
            THEN
                SELECT COUNT (*)
                  INTO ln_check
                  FROM g_emp emp
                 WHERE emp.deptno = :new.deptno AND emp.ename = :new.ename;

                DBMS_OUTPUT.put_line ('ln_check -> ' || ln_check);

                IF ln_check >= 1
                THEN
                    DBMS_OUTPUT.put_line ('AFTER ROW UNIQUE KEY ERROR');
                    raise_application_error (-20001, 'UNIQUE KEY VIOLATION');
                END IF;
            END IF;
        ELSIF UPDATING
        THEN
            IF NOT lb_ignore
            THEN
                NULL;
            END IF;
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line ('AFTER STATMENT');
    END AFTER STATEMENT;
END fn_emp_trigger;
/

SHOW ERRORS;

and Chris said...

Using triggers to enforce cross-row/table constraints is always tricky. It's tough to get these correct when many sessions are writing to the tables at the same time.

An alternative is to use (fast refresh on commit) materialized views. If you can write a query that returns the data you DON'T want in the MV, you can create a check constraint on it to reject these.

In this case you need a query that counts the employee rows/department. Then you can check this is one for all departments except sales.

It's unclear to me whether you can duplicate employee IDs across departments - I've assumed you can for this example.

So the MV query would be something like:

select e.empno, d.deptno, d.dname, count(*) c
from   emp e, dept d
where  e.deptno = d.deptno
group  by e.empno, d.deptno, d.dname;

Then you have a check constraint to check the count is one for non-sales departments and equal to itself for sales. Which could look something like:

check ( case 
    when dname = 'SALES' then c
    else 1
  end = c
)

Full demo below. I covered this topic in last month's Ask TOM Office Hours, so if you want more details on how this process works watch the recording:

create table dept(   
  deptno     number(2,0),   
  dname      varchar2(14),   
  loc        varchar2(13),   
  constraint pk_dept primary key (deptno)   
);

-- 2
 create table emp(   
  empno    number(4,0),   
  ename    varchar2(10),   
  job      varchar2(9),   
  mgr      number(4,0),   
  hiredate date,   
  sal      number(7,2),   
  comm     number(7,2),   
  deptno   number(2,0),   
  constraint fk_deptno foreign key (deptno) references dept (deptno)   
);

create materialized view log 
  on dept with primary key, rowid ( dname )
  including new values;

create materialized view log 
  on emp 
  with rowid ( empno, deptno )
  including new values;
  
create materialized view dept_emps 
refresh fast on commit 
as 
  select e.empno, d.deptno, d.dname, count(*) c
  from   emp e, dept d
  where  e.deptno = d.deptno
  group  by e.empno, d.deptno, d.dname;
  
alter table dept_emps 
  add constraint emp_dups_c 
  check ( case 
      when dname = 'SALES' then c
      else 1
    end = c
  );
  
insert into DEPT (DEPTNO, DNAME, LOC) 
values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept   
values(30, 'SALES', 'CHICAGO');

/* Initial row */
insert into emp ( empno, deptno )
values ( 7839, 10 );
commit;

/* Can't duplicate in dept = 10 */
insert into emp ( empno, deptno )
values ( 7839, 10 );
commit;

/* Can duplicate in dept = 30 */
insert into emp ( empno, deptno )
values ( 7839, 30 );
insert into emp ( empno, deptno )
values ( 7839, 30 );
commit;

select * from dept_emps;

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Conditional uniqueness not difficult to implement

mathguy, April 13, 2023 - 3:27 pm UTC

Triggers are a poor approach for many reasons. Materialized views are a good approach for complicated constraints, but it is overkill for conditional uniqueness on a single table.

Your question is not entirely clear (for example, it is not clear if there is a one-to-one correspondence between DEPTNO and DNAME). Assuming the most general case, where there is no required dependency between the two in either direction, and the columns may be nullable, you could do something like this:

create unique index i_uq_nonsales on emp(
  case when lnnvl(dname='SALES') then empno  end,
  case when lnnvl(dname='SALES') then deptno end
);

Whenever dname = 'SALES', the two expressions evaluate to NULL, and rows where all indexed expressions evaluate to NULL aren't included in the index. All other rows are indexed, and the uniqueness is enforced.

Note a few things: I used LNNVL(dname='SALES') to allow for the possibility that dname is NULL. Then: for department names other than SALES, a combination like empno=3404, deptno=NULL would be allowed, but only once. (That is how composite uniqueness works.) Also, if deptno doesn't determine dname, then you might have (3404, 30, SALES) and (3404, 30, MARKETING) in the table, this would not violate uniqueness. (If deptno determines dname, then you wouldn't even have to consider such a possibility.)

April 14, 2023 - 12:58 pm UTC

How does this work?

DNAME is in DEPT, not EMP; you cannot use it in the index

Thanks

Maddy K, April 13, 2023 - 9:10 pm UTC

Hi Chris, thanks a lot for your response.

I implemented this one differently.

I created a virtual column on the emp table and a Unique key constraint on this column. The virtual column is defined below.

 
 CREATE OR REPLACE FUNCTION DCS_HIST.fn_dept_dname_check (p_deptno IN VARCHAR2)
    RETURN VARCHAR2
    DETERMINISTIC
IS
    v_dname   VARCHAR2 (100);
BEGIN
    SELECT dname
      INTO v_dname
      FROM dept
     WHERE deptno = p_deptno;

    IF v_dname = 'SALES'
    THEN
        RETURN 'Y';
    ELSE
        RETURN 'N';
    END IF;
END;
/


CREATE TABLE dcs_hist.emp
(
    empno       NUMBER (4),
    ename       VARCHAR2 (100 BYTE),
    job         VARCHAR2 (9 BYTE),
    mgr         NUMBER (4),
    hiredate    DATE,
    sal         NUMBER (7, 2),
    comm        NUMBER (7, 2),
    deptno      NUMBER (2),
    uk_col      VARCHAR2 (500 BYTE)
                   GENERATED ALWAYS
                       AS (CASE "DCS_HIST"."FN_DEPT_DNAME_CHECK" (
                                    TO_CHAR ("DEPTNO"))
                               WHEN 'Y'
                               THEN
                                      TO_CHAR (ORA_HASH ("EMPNO"))
                                   || TO_CHAR ("DEPTNO")
                                   || "ENAME"
                               ELSE
                                   '1' || TO_CHAR ("DEPTNO") || "ENAME"
                           END)
);

CREATE UNIQUE INDEX dcs_hist.empukindex
    ON dcs_hist.emp (uk_col);

April 14, 2023 - 12:56 pm UTC

You have to be extremely careful doing this - you're lying to the database and it trusts you. This can lead to surprising results.

The function is declared deterministic but contains a SQL query. So it's non-deterministic.

The index is on EMP. So if you change data in DEPT, this is not reflected in the index. For example:

insert into DEPT (DEPTNO, DNAME, LOC) values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept values(30, 'SALES', 'CHICAGO');

insert into emp ( empno, deptno ) values ( 7839, 10 );

insert into emp ( empno, deptno ) values ( 7839, 30 );
commit;

select empno, uk_col from emp
where  uk_col = '118232151830';

     EMPNO UK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------- ---------------
      7839 118232151830

Dept 30 is currently SALES, so you get the hash. Update the department name to something other than SALES and the virtual column returns the department number:

update dept
set    dname = 'TEST'
where  deptno = 30;

select empno, uk_col from emp;

     EMPNO UK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------- --------
      7839 110                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
      7839 130

The problem here is the unique index still contains the original hash value. If you search for the hash, you'll get the row back - even though this is no longer the value for the virtual column:

select empno, uk_col from emp
where  uk_col = '118232151830';

     EMPNO UK_COL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
---------- --------------
      7839 118232151830

I would not rely on this solution.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Conditional uniqueness not difficult to implement

Thanks

More to Explore

PL/SQL demos

PL/SQL docs

Conditional unique basing upon existing data

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Conditional uniqueness not difficult to implement

Thanks

More to Explore

PL/SQL demos

PL/SQL docs