CODEWITHSUNDEEP
sqloracle-databaseplsql
bnabilos
bnabilos

Reputation: 2324

Adding a constraint in Oracle database

We want to add a constraint in a Oracle database to check if the employee's salary respects the hierarchy. An employee has an id, a name, a rank and a salary. We have 3 ranks (rank1, rank2, rank3), rank1 is superior to rank2 and rank2 is superior to rank3. When adding an employee that has rank2, his salary shouldn't be superior to a salary of an employee with rank1.

Can you tell us which solution is the best to implement this constraint ?

Thank you very much

Upvotes: 1

Views: 206

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I think you'll end up having to use a trigger, and since you'll need to refer to data within the same table on which the trigger exists it'll need to be a table trigger rather than a row trigger. Something like this:

CREATE OR REPLACE TRIGGER EMPLOYEES_AIU
  AFTER INSERT OR UPDATE ON EMPLOYEES
  -- Note: There is no FOR EACH ROW here so 'OLD' and 'NEW' values are not available
DECLARE
  strPrev_rank      EMPLOYEES.RANK%TYPE;
  nPrev_max_salary  EMPLOYEES.SALARY%TYPE;
BEGIN
  FOR aRow IN (SELECT RANK, MAX(SALARY) AS MAX_SALARY
                 FROM EMPLOYEES
                 GROUP BY RANK
                 ORDER BY RANK DESC)
  LOOP
    IF nPrev_max_salary IS NOT NULL AND
         aRow.MAX_SALARY > nPrev_max_salary THEN
      RAISE_APPLICATION_ERROR(-20101, 'Max salary (' || aRow.MAX_SALARY||
                                      ') of rank ' || aRow.RANK ||
                                      ' exceeds max salary (' || nPrev_max_salary ||
                                      ') of rank ' || strPrev_rank);
    END IF;

    strPrev_rank := aRow.RANK;
    nPrev_max_salary := aRow.MAX_SALARY;
  END LOOP;
END EMPLOYEES_AIU;

I've used similar triggers to check data validity and have found it works well.

Share and enjoy.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

It cannot be done with a check constraint, because these can only look at the values within the row being inserted or updated, not at other tables or other rows of the same table.

I have experimented with an approach using materialized views plus check constraints - see my blog. The trick is to create a materialized view of exceptions to the rule - i.e. a materialized view that should always be empty. Then add a check constraint to the MV that always fails e.g. CHECK(1=0).

For your situation the solution would look something like this:

create materialized view emp_emp_mv
refresh complete on commit as
select 1 dummy
from emp e1, emp e2
where e1.empno != e2.empno
and e1.rank < e2.rank
and e1.sal > e2.sal;

alter table emp_emp_mv
add constraint emp_emp_mv_chk
check (1=0) deferrable;

Please note that:

  1. I have not tested the example above
  2. I have never used this approach in a "real" system, only in experiments.

Upvotes: 3

Related Questions