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
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:
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.
